Expand JoinAssociation to two TableRecord rather than requiring a CTE

[dawilliams-gpsw]

Hey y'all! I was wondering if it would be possible to expand JoinAssociation to work beyond just CTEs. I think it can be useful to write an on the fly join across two tables based on a common column.

For example, I have a table "Foo" with two properties, id and value. I have another table named "Bar" with a bunch of properties, but it includes value as well. I would like to join these two tables over the value property. I imagine that I can go to my Foo and Bar models and add a link, via a hasOne relationship via that foreign key, but it doesnt seem possible to use the initializer to manually create it on the fly (because it's not public), nor can I use Foo.association(to: Bar.self, on: { ... }) because it requires CTEs. Perhaps there's a way to make another CTE that is just Bar.all() as the request, but I have been having trouble getting it to work the same way as a simple join, and in that case it seems like additional noise in code.

Is this restriction there for a reason, or would it be possible to open it up to all TableRecord conformers?

[groue]

Hello @dawilliams-gpsw,

You can create an association even if there is no foreign key in the database, with an explicit ForeignKey:

extension Foo: TableRecord {
    static let barForeignKey = ForeignKey(["value"], to: ["value"])
    static let bar = belongsTo(Bar.self, using: barForeignKey)
}

// SELECT foo.*, bar.*
// LEFT JOIN bar ON bar.value = foo.value
// ...
Foo.including(optional: Foo.bar)...

If you need to perform a join with a condition that is not an equality check, such as LEFT JOIN bar ON bar.score + bar.bonus > foo.threshold, then the current api won't let you proceed indeed. I hope this is not the case 😅

[groue]

For the record, JoinAssociation accepts any kind of join condition (it is defined with a closure that can return any expression), and this makes it a very special kind of association.

Here are a few apis that JoinAssociation does not or can not support, unlike other associations:

// Hypothetical support for JoinAssociation on regular tables
extension Foo {
    static let bar = association(to: Bar.self, on: { foo, bar in
        bar["score"] + bar["bonus"] > foo["threshold"]
    })
}

// Likely possible, but not implemented:
// SELECT * FROM bar WHERE bar.score + bar.bonus > 1000
let foo = Foo(threshold: 1000)
let bar = try foo.request(for: Foo.bar).fetchOne(db)

// Not implemented (and I have no clue how to do it),
// the has-many facet:
Foo.including(all: Foo.bar)
Foo.having(Foo.bar.isEmpty)
// etc

This explains why JoinAssociation is limited to CTEs.

[mallman]

@dawilliams-gpsw

To borrow from Poker parlance, I'll see your TableRecord and raise you a QueryInterfaceRequest.

This extension allows a join from one QueryInterfaceRequest<RowDecoder> to a QueryInterfaceRequest<Destination>:

extension QueryInterfaceRequest {
    public func association<Destination>(
        to queryInterfaceRequest: QueryInterfaceRequest<Destination>,
        on condition: @escaping (_ left: TableAlias, _ right: TableAlias) -> SQLExpressible)
    -> JoinAssociation<RowDecoder, Destination>
    {
        JoinAssociation(
            to: queryInterfaceRequest.relation,
            condition: .expression { condition($0, $1).sqlExpression })
    }
}

Unfortunately, because it uses a module-private constructor for JoinAssociation, it cannot be defined in user code. I have this code snippet defined in my clone of GRDB.swift.

Personally, I use this for joining between two instances of QueryInterfaceRequest<Row>. It has worked for me so far.

I asked @groue if he would add this capability to GRDB. This was a long time ago, but my recollection is that he declined. :(

[groue]

Thanks for the follow-up, @mallman :-)

Those associations do not come with the full feature set of associations.

In particular, the request(for:) method crashes with a "Not implemented" fatal error. Such holes are the reason why JoinAssociation is restricted to CTEs.

Since this is a "Not implemented" fatal error, maybe it can be implemented in a correct way. In such case, the restriction would be lifted.

EDIT - my initial answer above describes more non-implemented features.

[mallman]

Hi @groue. Your observation regarding the unimplemented capabilities of this kind of join association is on point. Fortunately for me, I have not had a need for any such capabilities yet.

FWIW I do make heavy use of CTEs with GRDB, including joins. They took a bit of getting used to, but I've come to appreciate them very much.

In particular, I've found CTEs to be much cleaner than subqueries. Of course, this is not just a GRDB thing but a SQL thing. Those SQL inventors are quite clever...

[groue]

A pull request that completes those association would be welcome. It's probably not trivial, otherwise I would have done it 😉

[dawilliams-gpsw]

Woah, I never realized you had replied to the original message @groue, my apologies for not following up here. I need to dig back into this use case and see if I still need it, but appreciate the response! Cheers