Filter for Second Parent in Fluent

Related Projects Vapor
1

Dear All,

I am probably missing something very basic, but can't seem to find the way to filter with more than one parent, if the child has more than one parents, like this:

User -- parent of --> Ballot <-- parent of -- Vote

If I want to find the ballot of a user for a given vote, I can let ballot = try await vote.$ballots.query(on: req.db) -- but then what?

Intuitively, I would write something like this:

let ballot = try await vote.$ballots.query(on: req.db).filter(\.$user.id == loggedInUser.id).first, but that is of course not correct.

I guess, in general, how to filter a @Parent without adding a @Children field to the parent?

Thanks,
Thomas

import Fluent
import Vapor

final class Ballot: Model, @unchecked Sendable {
    static let schema = "ballots"
    
    @ID(custom: "id", generatedBy: .database)
    var id: Int32?
    
    @Parent(key: "vote_id")
    var vote: Vote
    
    @Parent(key: "user_id")
    var user: User
    
    // ...
}
2

I realise I have asked the wrong question.

let ballot = try await vote.$ballots.get(on: req.db).filter{ $0.$user.id == loggedInUser.id }.first works...

But what I was really after was what is executed by the SQL server and what is done programmatically.

My understanding is that vote.$ballots.get(on: req.db) is translated into SQL and done by the server, everything else is programmatic.

So the correct question to ask would have been: is there a way to write this so that it would translate into SQL and gets run by the server?

3

Yeah essentially .get(on:) is syntactic sugar to get all of the ballots. The syntax you're looking for is vote.$ballots.query(on: req.db) which returns a QueryBuilder you can then add filters to, such as

let ballot = try await vote.$ballots.query(on: req.db).filter(\.$user.$id == loggedInUser.id).first()

This then gets translated into SELECT * FROM Ballots WHERE voteID = <VOTE_ID> AND userID = <LOGGED_IN_USER_ID> LIMIT 1

You can see this by enabling query logging and see what the generated query is.

4

Yes! That is what I tried first, but it does not compile:

let ballot = try await vote.$ballots.query(on: req.db).filter(\.$user.id == loggedInUser.id).first

causes Cannot infer key path type from context; consider explicitly specifying a root type against the .filter(

Changing to \Ballot.$user.id == is no good, because now we get the reference and the error is Binary operator '==' cannot be applied to operands of type 'ReferenceWritableKeyPath<Ballot, Int32>' and 'Int32'

This is what I have run into other cases where I wanted to directly filter on a foreign key.

I am sure I am missing something really basic...

5

You're missing the $ on id.

Copy it exactly as Tim has it:
filter(\.$user.$id == loggedInUser.id)

6

Oh, bother... I knew it was something really basic. Have been looking at that line of code for too long!

Thanks!