Find all items with empty many to many relationship in vapor fluent

Question

Given the example from the vapor docs:

// Example of a pivot model.
final class PlanetTag: Model {
    static let schema = "planet+tag"

    @ID(key: .id)
    var id: UUID?

    @Parent(key: "planet_id")
    var planet: Planet

    @Parent(key: "tag_id")
    var tag: Tag

    init() { }

    init(id: UUID? = nil, planet: Planet, tag: Tag) throws {
        self.id = id
        self.$planet.id = try planet.requireID()
        self.$tag.id = try tag.requireID()
    }
}

final class Planet: Model {
    // Example of a siblings relation.
    @Siblings(through: PlanetTag.self, from: \.$planet, to: \.$tag)
    public var tags: [Tag]
}

final class Tag: Model {
    // Example of a siblings relation.
    @Siblings(through: PlanetTag.self, from: \.$tag, to: \.$planet)
    public var planets: [Planet]
}

How can I query with fluent all planets which do not have a tag?

Planet.query(on: req.db).filter(?).all()

EDIT: Solution but with async/await

let pivots = try await PlanetTag.query(on: req.db).unique().all()
let planetsWithTags = pivots.map { $0.$planet.id }
let planetsWithoutTags = Planet.query(on: req.db).filter(\.$id !~ planetsWithTags)
    
return try await planetsWithoutTags.paginate(for: req).map(\.detail)

Answer 1

I haven't checked this out but, intuitively, this should work if you want to make use of the Sibling relationship:

Planet.query(on:req.db).with(\.$tags).all().map { allPlanets in
    let planetWithoutTags = allPlanets.filter { $0.tags.isEmpty }
    // continue processing
}

This is a bit wasteful, since you are fetching (potentially) lots of records that you don't want. A better approach is to get the planets that do have tags and then exclude those:

PlanetTag.query(on:req.db).unique().all(\.$planet).flatMap { planetTags in
    return Planet.query(on:req.db).filter(\.$id !~ planetTags.map { $0.$planet.$id }).all().flatMap { planetsWithoutTags in 
        // continue processing
    }
}