Grdb.swift: Support: Can't select specific columns from a table

[Mina-R-Meshriky]

Dear @groue

I have a record setup as following, using the latest version of GRDB
`class StoreEntry : Record {
static let TABLE = "Store"
static let ID = "id"
static let COLUMN_NAME = "name"
static let COLUMN_SORTING = "sorting"
static let COLUMN_COUNTRY_FK = "country_fk"

static func createTable(db: Database) {
    do {
        try db.execute(" CREATE TABLE IF NOT EXISTS \(TABLE) ( " +
            "\(ID) INTEGER, " +
            "\(COLUMN_NAME) TEXT NOT NULL, " +
            "\(COLUMN_SORTING) INTEGER NOT NULL, " +
            "\(COLUMN_COUNTRY_FK) INTEGER NOT NULL, " +
            "CONSTRAINT storeKey PRIMARY KEY (\(ID)) ON CONFLICT REPLACE, " +
            "CONSTRAINT has_store FOREIGN KEY (\(COLUMN_COUNTRY_FK)) REFERENCES \(CountryEntry.TABLE) (\(CountryEntry.ID)) ON DELETE CASCADE ON UPDATE CASCADE ); " +
            "CREATE INDEX IF NOT EXISTS IX_ForeignCountryForStore ON \(TABLE) (\(COLUMN_COUNTRY_FK)) ; ")
    } catch {
        print("Unable To create Table \(TABLE)")
    }
}
static func dropTable(db: Database) {
    do {
        try db.execute(" DROP INDEX IF EXISTS IX_ForeignCountryForStore; " +
            "DROP TABLE IF EXISTS \(TABLE);")
    } catch {
        print("Unable to Drop table \(TABLE)")
    }
}

var id: Int64
var name: String
var sorting: Int64
var country_fk : Int64

override class var databaseTableName: String {
    return TABLE
}

init(id: Int64, name: String, sorting: Int64, country_fk: Int64) {
    self.id = id
    self.name = name
    self.sorting = sorting
    self.country_fk = country_fk
    super.init()
}

required init(row: Row) {
    id = row.value(named: StoreEntry.ID)
    name = row.value(named: StoreEntry.COLUMN_NAME)
    sorting = row.value(named: StoreEntry.COLUMN_SORTING)
    country_fk = row.value(named: StoreEntry.COLUMN_COUNTRY_FK)
    super.init(row: row)
}

override var persistentDictionary: [String : DatabaseValueConvertible?] {
    return [
        StoreEntry.ID : id,
        StoreEntry.COLUMN_NAME : name,
        StoreEntry.COLUMN_SORTING : sorting,
        StoreEntry.COLUMN_COUNTRY_FK : country_fk
    ]
}

}`

And I tried query for the ID and Name Only from that record as follows :
do { try dbQueue.inDatabase { db in let stores = try StoreEntry.select(sql: "\(StoreEntry.ID), \(StoreEntry.COLUMN_NAME)").filter(sql: "\(StoreEntry.COLUMN_COUNTRY_FK) = ? AND name GLOB ?", arguments: ["21","*H*"]).fetchCursor(db) } catch { print("Couldn't get the stores") print(SqlString) }
the program crash with the following (Note I have config.trace turned on) :

SELECT id, name FROM "Store" WHERE country_fk = '21' AND name GLOB '*H*' fatal error: no such column: sorting: file /Users/XXX/Desktop/XXX/XXX/Final/XXX/Pods/GRDB.swift/GRDB/Core/Row. .swift, line 303

but when I ask for all i.e. Select * From Table it works fine

[groue]

Hello again @Mina-R-Meshriky :-)

Here is my understanding of your issue. So, your application runs the following request:

// SELECT id, name FROM "Store" WHERE country_fk = '21' AND name GLOB '*H*'

let stores = try StoreEntry
    .select(sql: "\(StoreEntry.ID), \(StoreEntry.COLUMN_NAME)")
    .filter(sql: "\(StoreEntry.COLUMN_COUNTRY_FK) = ? AND name GLOB ?",
            arguments: ["21","*H*"])
    .fetchCursor(db)

When the cursor is consumed, GRDB loads rows from the database, and converts each one of them into a StoreEntry record.

The relevant part of StoreEntry is:

class StoreEntry : Record {
    ...
    var sorting: Int64
    var country_fk : Int64

    required init(row: Row) {
        ...
        sorting = row.value(named: StoreEntry.COLUMN_SORTING)
        country_fk = row.value(named: StoreEntry.COLUMN_COUNTRY_FK)
        ...
    }
}

The sorting and country_fk properties are non-optional. They need a value, badly. When a fetched row has no value for the sorting and country_fk columns, GRDB can't choose which value it should pick, and crashes with a fatal error: "no such column: sorting".

A fatal error in GRDB means that something has to be changed, in the program, or the database. Here this is the program that has to be changed.

You have three ways to solve this out:

1. Load all columns required by StoreEntry:

   let stores = try StoreEntry
       .filter(sql: "\(StoreEntry.COLUMN_COUNTRY_FK) = ? AND name GLOB ?",
               arguments: ["21","*H*"])
       .fetchCursor(db)
   
   while let store = try stores.next() {
       ...
   }
   

2. Change StoreEntry so that sorting and country_fk are optional properties (GRDB turns missing columns into nil, in order to support this use case):

   class StoreEntry : Record {
       var sorting: Int64?     // Optional
       var country_fk : Int64? // Optional
   }
   
   let stores = try StoreEntry
       .select(sql: "\(StoreEntry.ID), \(StoreEntry.COLUMN_NAME)")
       .filter(sql: "\(StoreEntry.COLUMN_COUNTRY_FK) = ? AND name GLOB ?",
               arguments: ["21","*H*"])
       .fetchCursor(db)
   
   while let store = try stores.next() {
       store.sorting    // nil
       store.country_fk // nil
   }
   

3. Define a new record type, dedicated to the restricted select:

   struct SimpleStoreEntry: RowConvertible {
       var id: Int64
       var name: String
   
       init(row: Row) {
           id = row.value(named: StoreEntry.ID)
           name = row.value(named: StoreEntry.COLUMN_NAME)
       }
   }
   
   let stores = try StoreEntry
       .select(sql: "\(StoreEntry.ID), \(StoreEntry.COLUMN_NAME)")
       .filter(sql: "\(StoreEntry.COLUMN_COUNTRY_FK) = ? AND name GLOB ?",
               arguments: ["21","*H*"])
       .asRequest(of: SimpleStoreEntry.self)
       .fetchCursor(db)
   
   while let simpleStore = try stores.next() { // simpleStore is SimpleStoreEntry
       ...
   }
   

You know better which one is the best solution for your application.

[Mina-R-Meshriky]

yeah :) I am asking a lot of questions right !!
Ok, then according to your answer .. whenever I create a record I should put any Column that I might not call in a select statement as an optional right, because the record must load as a whole

[groue]

whenever I create a record I should put any Column that I might not call in a select statement as an optional right, because the record must load as a whole

Yes, that's option 1. Option 2 allows to load records will nil properties for missing columns. And option 3 loads restricted records with less properties.

[groue]

I am asking a lot of questions right !!

That's how we all improve! I'm happy that GRDB has a solution for all your needs so far.

[Mina-R-Meshriky]

Hi again @groue :)

I've been scratching my head a bit because of the following problem that is related to this question:

when I run this SQL

 SELECT 
       post.id_post,
       .....
 FROM 
     Post  
         INNER JOIN X
         ON  .... 
         LEFT JOIN Y 
         ON  ....
         INNER JOIN (  
                 Z  
              LEFT JOIN A 
              ON  ....
          ) ON  ....
         LEFT JOIN B 
         ON  ....
         LEFT JOIN C
         ON  .... 
 GROUP BY Post.1, Post.2

and I call it like this :

    Post.fetchAll(db, aboveSQL)

it gives me the following error:

 fatal error: no such column: id_post: file /xxx/GRDB/Core/Row.swift, line 303

now the sql itself gets the correct values if I ran it on "SQLPro" so the sql is correct ... the problem is GRDB asks for the id_post while it is given in the select statement

and when I change the id_post to optional it returns nill and according to the explanation above it should work without an optional value

[groue]

the problem is GRDB asks for the id_post while it is given in the select statement

Well, GRDB asks for the id_post column only if the application does. Likely in the Post initializer:

id_post = row.value(named: "id_post")

and when I change the id_post to optional it returns nill and according to the explanation above it should work without an optional value

If the row can't return the type that the application asks for, GRDB ends with a fatal error. Non-optional types absolutely require that a column is present, and does not contain a NULL value. Reminder: in Swift, an optional type can store the special value nil (meaning "no value"), when non-optional types must have a value. GRDB returns nil (for optional types), when a database row contains NULL or does not contain a column.

struct S {
    let optionalInt: Int?
    let int: Int
    init {
        optionalInt = nil // OK
        int = nil // not OK: that's how Swift works.
    }
    init(row: Row) {
        // OK if the column a is missing or contains NULL
        optionalInt = row.value(named: "a")
        // Absolutely requires that column b is present, and does not contain NULL
        int = row.value(named: "b")
    }
}

[Mina-R-Meshriky]

Well, GRDB asks for the id_post column only if the application does. Likely in the Post initializer:

yes I do initialize it

Actually, I figured out the problem:
when the records are fetched the columns where named with the syntax : Table.columnName not just columnName
so I just used an alias in the select statement : Select Table.columnName AS columnName
and that solved it. I think this is because I was consuming columns from different tables.

off course I had to use different columnNames for each table that way