Vapor Fluent OR relationship

Question

I have a two models in Vapor using Fluent as the ORM, one is a Transaction and the other is an Invoice. The Transaction model has the below:

@OptionalParent(key: "invoice_id")
var invoice: Invoice?

So the invoice property can be nil or assigned to an Invoice model. My question is how do I query for Transaction's where the invoice can be nil OR can be assigned to an Invoice where I can check if the invoice is active (I have an isActive property on the Invoice model)?

I tried reading about the below:

https://docs.vapor.codes/fluent/query/

https://forums.swift.org/t/vapor-fluent-query-support-and-xor/32647

So far I'm trying to do like so:

try? await Transaction.query(on: request.db)
    .with(\.$invoice)
    .sort(\.$dateCreated, .descending)
    .all()

I'm then looping through all the transactions and I'm skipping what is not having isActive set to true.

I also tried the following:

    let transactions = (try? await Transaction.query(on: request.db)
        .with(\.$invoice)
        .group(.or) { group in
            group
                .filter(\Transaction.$invoice.$id !~ (try inactiveInvoices.map { try $0.requireID() }))
                .filter(\Transaction.$invoice.$id == nil)
        }
        .sort(\.$dateCreated, .descending)
        .all()) ?? []

It works...but it has a lot of queries in it. Is there a way to simplify it?

I basically want to check if it's nil or if the isActive property on invoice is true from the query itself. Is this possible in Vapor? Any help is appreciated!

Answer 1

I got a solution that works! I need to use a LEFT OUTER JOIN which can be done by specifying .left for the method argument on the join, like so:

let transactions = (try? await Transaction.query(on: request.db)
.with(\.$invoice)
.join(Invoice.self, on: \Transaction.$invoice.$id == \Invoice.$id, method: .left)
.group(.or) { group in
    group
        .filter(Invoice.self, \Invoice.$isActive == true)
        .filter(\.$invoice.$id == nil)
}
.sort(\.$dateCreated, .descending)
.all()) ?? []

Answer 2

You can do this in a filter on the original query, after the with:

try? await Transaction.query(on: request.db)
    .with(\.$invoice)
    .group(.or) { group in
        group.filter(\.$invoice == nil).filter(\.$invoice.$isActive == true)
    }
    .sort(\.$dateCreated, .descending)
    .all()

I have a feeling the seemingly redundant == true is needed here, but can't check at the moment.