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)

Nick · Accepted Answer · 2022-04-12T10:52:59.760

2

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
    }
}

edited Apr 12 '22 at 10:52

answered Apr 12 '22 at 10:37

Nick

4,820
18
31
47

The problem with this approach is that I want to paginate the queried items via `QueryBuilder.paginate(on: req) -> Page`. I can't call this method after calling .all() effectively mapping my `QueryBuilder` to `[Model]`. I also can't paginate first and then call .all() and filter() since there is no filter method for a Page. This would otherwise mess with the number of items each page should have. – dehlen Apr 12 '22 at 11:28
Hang on, why can't you call paginate in the inner query of my 'better' approach? You don't call `paginate` after `all` but instead of it. – Nick Apr 12 '22 at 11:37
Ah yes, thank you I just did not see that this would work. Thank you very much. I updated your snippet accordingly to make use of the new async/await API instead of using flatMap and added it as an edit for further reference. – dehlen Apr 12 '22 at 11:53

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

1 Answers1