Error for query count grouped

[ahartman]

What did you do?

Tried selecting counts, grouped by a column

What did you expect to happen?

Counts by column

What happened instead?

Error: Agenda_Assistent/DBModel.swift:169: Fatal error: column not found: "id" - row: [visitDate:"2017-01-03 18:00:00.000" COUNT("visitAge"):1], sql: SELECT "visitDate", COUNT("visitAge") FROM "visit" GROUP BY "visitDate", arguments: []

Environment

GRDB flavor(s): GRDB GRDB version: master Installation method: SPM Xcode version: latest Swift version: 5 Platform(s) running GRDB: macCatalyst **macOS version running Xcode: latest

Demo Project

As always, the question is: what am I doing wrong?

I have the following struct, table and query:

struct Visit: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    var id: String
    var modifiedDate: Date?
    var visitAge: Int
    var visitCalendar: String
    var visitCanceled: Bool
    var visitCreated: Date
    var visitDate: Date
    var visitFirst: Bool
    var visitNoShow: Bool
    var visitRecurrent: Bool
    var visitPatientName: String
    var patientId: Int
}

try db.write { db in
                try db.create(table: "visit", ifNotExists: true) { t in
                    t.column("id", .text)
                        .primaryKey()
                        .indexed()
                    t.column("modifiedDate", .text)
                        .indexed()
                    t.column("visitAge", .integer)
                    t.column("visitCalendar", .text)
                        .indexed()
                    t.column("visitCreated", .text)
                    t.column("visitCanceled", .boolean)
                    t.column("visitDate", .text)
                        .indexed()
                    t.column("visitFirst", .boolean)
                    t.column("visitNoShow", .boolean)
                    t.column("visitRecurrent", .boolean)
                    t.column("visitPatientName", .text)
                    t.belongsTo("patient", onDelete: .cascade)
                }
            }

func getVisitAges() -> [Visit] {
        var visitInfo = [Visit]()
        do {
            visitInfo = try db.read { db in
                try Visit
                    .select(
                        Column("visitDate"),
                        count(Column("visitAge"))
                    )
                    .group(Column("visitDate"))
                    .asRequest(of: Visit.self)
                    .fetchAll(db)
            }
        } catch {
            fatalError("\(error)")
        }
        return visitInfo
    }

and the results is the error above.

Regards, André Hartman

[ahartman]

Dear Gwendal,

I arrived at another 'modified' example that works:

      struct VisitAgeCount: Decodable, FetchableRecord {
        var visitAge: Int
        var countVisitAge: Int
    }

    func getVisitAges(dates: PeriodStartEnd) {
        var visitAges = [VisitAgeCount]()
        do {
            try db.read { db in
                let request = Visit
                    .select(
                        Column("visitAge"),
                        count(Column("visitAge"))
                    )
                    .group(Column("visitAge"))

                //let temp = try VisitAgeCount.fetchAll(db, request)

                let visitAge = try Row.fetchAll(db, request)
                for v in visitAge {
                    visitAges.append(VisitAgeCount(visitAge: v[0], countVisitAge: v[1]))
                }
            }
        } catch {
            fatalError("\(error)")
        }
        print(visitAges)
    }

However, I cannot map the fetchAll to the struct above as I cannot guess the name for the count(visitAge) variable. The line 'let temp = ...' fails with an error, probably because the variable name 'countVisitAge' is not correct.

Regards, André Hartman

[groue]

Hello @ahartman

and the results is the error above.

Indeed. The request selects two columns when the record you want to decode from its fetched results counts a dozen properties.

I'm far away from any computer right now. May I suggest you log SQL statements so that you can check if the requests match the decoded records?

[ahartman]

Dear Gwendal,

Being away from your computer is a good thing, take your time.

My query selects only two columns but into a struct that also holds the 'same' two columns.

My problem can be observed in the screen image below:

Two, very similar queries are visible, 'getVisitAges' and 'getVisitMinMax'. In the log below that 'getVisitMinMax' generates a query with aliases, making it easy to derive the corresponding variable name in the struct. The query for 'getVisitAges', however, is generated without aliases so I cannot determine the receiving variable name. The returned data comes back with a name as 'count("visitAge") and that is not a valid variable name.

Regards, André Hartman

[groue]

You can give a name to any selected expression with the AS SQL operator. The Swift version is called forKey, because, well, one usually names a column so that it fits a decoding key. See SQL operators.