Vapor / Fluent: filtering nested requests

I have a Fluent query that looks like this:

            var uQuery = User.query(on: req.db)
                .filter(\.$id == user.id!)
                .with(\.$playerTournaments) {$0.with(\.$tournamentGames)}
                .all()

A player Tournament has multiple Matches, which contain Games. In this context, I don't care about the Matches, so $tournamentGames allows me to get the games for the tournament directly.

But what I want is to filter the games returned by $tournamentGames by the @Parent property $player, so that only the games for that player are returned. How can I do this? I'm assuming there's a syntax along the lines of

.with(\.$playerTournaments) {$0.with(\.$tournamentGames) { $0.filter(\.$id == user.id!}}

but I haven't been able to figure it out...

EDIT: Relationships between objects

User defines a many-to-many with Tournaments:

      through: TournamentPlayerPivot.self,
      from: \.$player,
      to: \.$tournament)
    var playerTournaments: [Tournament]```

Tournament has a reciprocal relationship which doesn't come into this, but a relationship with Game :

      through: TournamentGamePivot.self,
      from: \.$tournament,
      to: \.$game)
    var tournamentGames: [Game]```

 Tournament is @Parent to ```Match```, which in turn parents ```Game```. Match manages a bunch of non-user-facing stuff to do with determining when tournaments end, but are in the way here.

A ```Game``` has a $player field, which contains the uuid of the User assigned to play it. And a Tournament has an $owner, guaranteed to to be a player/User. Both are one to one.

More questions, happy to answer.

Solution

You have two options - you either eager load all the data and filter the relationships in Swift using array filters or you use joins. Joins will be more efficient as they'll only return what you want and databases are designed to do them as efficiently as possible (at least SQL databases).

The docs for joins are https://docs.vapor.codes/4.0/fluent/query/#join