Constant ForeignKey value

[seanmrich]

I'm looking to join with a table where one of the columns doesn't have an associated column in the joined table. I want to use a constant value for that column. I know that probably doesn't make sense so here's some detail.

I have a table that maps CloudKit ids to local database ids. It works across several tables since CKSyncEngine only provides the id without the type of record (ie which table it goes with). Having a single table lets me find the right local record for any CloudKit record id. Here's a sample schema to give you the idea:

try db.create(table: "players") { t in 
  t.autoIncrementedPrimaryKey("id")
  t.column("teamId", .integer)
    .notNull()
    .references("teams", onDelete: .cascade)
  t.column("deleteDate", .date)
}
try db.create(table: "teams") { t in 
  t.autoIncrementedPrimaryKey("id")
  t.column("name", .text)
}
try db.create(table: "mapping") { t in 
  t.primaryKey {
    t.column("id", .integer)
    t.column("table", .text)
  }
  t.column("cloudId", .text)
}

Now let's say I want to query for the number of players that have a cloudId in the mapping table. In SQL I'd do something like this:

SELECT count(*)
FROM (
  SELECT p.id
  FROM players p
  LEFT JOIN mapping m ON m.id = p.id AND m."table" = 'players'
  WHERE p.deleteDate IS NULL AND m.id IS NOT NULL
)

I tried doing this in query syntax, but I'm not sure how to refer to the table column since it doesn't refer to a column in a table but rather the table name itself. Is it possible to set up an association like this?

[groue]

Hello @seanmrich,

Your solution is to define an association based the id column, and filtered on the table column:

extension Player {
    static let mapping = hasOne(Mapping.self, using: ForeignKey(["id"]))
        .filter(Column("table") == databaseTableName)
}

Note that I've used hasOne because each player has zero or one matching row in the mapping table, thanks to the primary key.

I can reproduce your target SQL query:

// SELECT COUNT(*) FROM (
//     SELECT "players"."id"
//     FROM "players"
//     LEFT JOIN "mapping" ON ("mapping"."id" = "players"."id") AND ("mapping"."table" = 'players')
//     WHERE ("players"."deleteDate" IS NULL) AND ("mapping"."rowid" IS NOT NULL)
// )
let mappingAlias = TableAlias()
let count = try Player
    .selectPrimaryKey()
    .joining(optional: Player.mapping.aliased(mappingAlias))
    .filter(Column("deleteDate") == nil && mappingAlias.exists)
    .fetchCount(db)

The filtered left join is essentially an inner join, so we can simplify the request:

// SELECT COUNT(*) FROM (
//     SELECT "players"."id"
//     FROM "players"
//     JOIN "mapping" ON ("mapping"."id" = "players"."id") AND ("mapping"."table" = 'players')
//     WHERE "players"."deleteDate" IS NULL
// )
let count = try Player
    .selectPrimaryKey()
    .joining(required: Player.mapping)   // required instead of optional
    .filter(Column("deleteDate") == nil) // no need to check for mapping existence
    .fetchCount(db)

[groue]

If you do not want to repeat the mapping association in Player, Team, and other mapped records, you can share its definition in a dedicated protocol:

/// A type that has matching entries in the `mapping` table.
protocol MappedRecord: TableRecord { }

extension MappedRecord {
    /// The association to the associated mapping.
    static var mapping: HasOneAssociation<Self, Mapping> {
        hasOne(Mapping.self, using: ForeignKey(["id"]))
            .filter(Column("table") == databaseTableName)
    }
}

extension Player: MappedRecord { }
extension Team: MappedRecord { }

[seanmrich]

This is terrific! I must admit I had to spend some time going through the documentation about TableAlias to understand how this works, but it's incredible how powerful and flexible the framework is. Thanks so much!

[groue]

Your questions are welcome! Thanks for the compliment, and happy GRDB :-)