Support for query within query

[tarun-personatech]

I am trying to run the below SQL query

SELECT * FROM
(
    SELECT 
    agi.id as agi_id,
    agi.day as agi_day,
    agi.date as agi_date,
    agi.type as agi_type,

    ts.id as ts_id,
    ts.title as ts_title,
    ts.description as ts_description

    FROM agendaItem as agi 

    LEFT JOIN trackSession as ts ON agi.id = ts.agendaItemId
) 

WHERE 
CASE WHEN agi_type = 'Track session' 
THEN 
    EXISTS(SELECT agendaItemId FROM trackSession WHERE agendaItemId = agi_id)
ELSE 
    1 
END

My AgendaItem and TrackSession entities are properly set up with confirmations to Codable, Fetchable, TableRecords, EncodableRecord.

AgendaItem -> (id, day, date, type, .....<more>)
TrackSession -> (id, title, description, .....<more>.)

I want to fetch records into AgendaItemInfo which is set up like this:

struct AgendaItemInfo: TableRecord, FetchableRecord, Codable {
    let agendaItem: AgendaItem
    let trackSession: TrackSession?
}
extension AgendaItemInfo {
    private enum Scopes: String, CaseIterable {
        case AgendaItem
        case TrackSession
    }

    init(row: Row) {
        agendaItem = row[Scopes.AgendaItem.rawValue]
        trackSession = row[Scopes.TrackSession.rawValue]
    }
}

Now ideally I would want to create a request for this query and do AgendaItemInfo.fetchAll(db) but I couldn't find any way so I am creating the request for the inner select query and then converting it into a sql string and feeding into the whole sql string like this:

extension AgendaItemInfo {

static func fetchAll(_ db: Database) throws -> [AgendaItemInfo] {

let alias = TableAlias(name: "agi")
let aliasedAgendaItem = AgendaItem.aliased(alias)
let request = aliasedAgendaItem.select(idColumn, dayColumn, dateColumn, typeColumn)
                      .including(optional: AgendaItem.trackSession.select(idColumn,titleCol,desCol))
                       .asRequest(of: AgendaItemInfo.self)

let adapters = splittingRowAdapters(columnCounts: [
            AgendaItem.selectedColumns().count,   // AgendaItem Table
            TrackSession.selectedColumns().count  // TrackSession Table
            ]
        )

let scopeRawValues:[String] = Scopes.allCases.map { $0.rawValue }
let adapterMapping = Dictionary(keys: scopeRawValues, values: adapters)
let adapter = ScopeAdapter(adapterMapping)

let sqlString = try SQLRequest(db, request: request).sql
let sql = "Select * from (\(sqlString)) where case when ........"

return try AgendaItemInfo.fetchAll(db, sql: sql, adapter: adapter)
}

The statement SQLRequest(db, request: request).sql generated a SQL string like this: select agi.id, agi.day ..... from agendaItem agi .......

This code compiles but the query fails with No column agi_type which is understandable as the outer part of the query will not know agi_type of inner query and fails. I couldn't find any way to alias the columns to look like agi.id as agi_id. Is there any way?

So if I remove the request and play with the plain SQL, the query works, results are also fetched but the decoding fails as fetched rows contain keys like agi_id, agi_day, .... which fail to decode into AgendaItem and TrackSession structs.

So both the cases fail. Is there any way I can do this other than manually parsing all the rows into respective instances?

Environment

GRDB flavor(s): GRDB GRDB version: 4.2.1 Installation method: CocoaPods Xcode version: 10.3 Swift version: 5.0 Platform(s) running GRDB: iOS

[groue]

Hello @tarun-personatech

The raw SQL query you want to run is indeed not directly expressible with the query interface. Thanks for having tried a couple other solutions.

I couldn't find any way to alias the columns to look like agi.id as agi_id. Is there any way?

Yes: use the forKey method:

// SELECT agi.id AS agi_id ...
AgendaItem.select(idColumn.forKey("agi_id"), ...)

So if I remove the request and play with the plain SQL, the query works, results are also fetched but the decoding fails as fetched rows contain keys like agi_id, agi_day, .... which fail to decode into AgendaItem and TrackSession structs.

Yes as well. Decodable records look for columns with the same name as their properties. This is how the standard Decodable protocol works.

Maybe the simplest solution for you would be to stop using Decodable, and simply perform manual decoding with the raw FetchableRecord protocol:

init(row: Row) {
    // who cares about mismatching property and column names?
    self.foo = row["bar"] 
    ...
}

[tarun-personatech]

@groue Thank you very much for the response, especially for AgendaItem.select(idColumn.forKey("agi_id"), ...). I should not have missed that.

Meanwhile, I found my way around the query within the query. I also created a whole infrastructure for query within the query (for my specific use case). I'll try to put both the demo projects for reference in some time.

[groue]

Thanks for your kind response, @tarun-personatech :-) And remember that when our infrastructures turn simple SQL queries into an opaque pudding of layered values, we may have lost something 😉 Have a good exploration 👍