CODEWITHSUNDEEP
swiftfluentvapor
kstefanou
kstefanou

Reputation: 617

Swift Vapor: Query filter siblings

I'm trying to filter my results based on siblings relationship.

Customer Model

final class Customer: Model, Content {
    
    static var schema: String = "customer"
    
    @ID(custom: .id) 
    var id: ObjectId?

    @Siblings(through: BranchCustomer.self, from: \.$customer, to: \.$branch) 
    var branches: [Branch]
   }

Branch Model

final class Branch: Model, Content {
    
    static let schema: String = "branch"
    
    @ID(custom: .id) 
    var id: ObjectId?
    
    @Siblings(through: BranchCustomer.self, from: \.$branch, to: \.$customer) 
    var customers: [Customer]
}

Branch+Customer Pivot

final class BranchCustomer: Model {
    
    static var schema: String = "branch+customer"
    
    @ID(custom: .id) 
    var id: ObjectId?

    @Parent(key: "branch_id") 
    var branch: Branch

    @Parent(key: "customer_id") 
    var customer: Customer
}

I want to create a query that will return me all the customers that are in one or more of the specified branches, something like the following (of course the following code doesn't compile):

let query = Customer
     .query(on: request.db)
     .filter(\.$branches ~~ [branch1, branch2])

As I red in the documentation I couldn't find any solution to fit my needs, I also tried to query pivot table instead like that:

let query = BranchCustomer
      .query(on: request.db)
      .group(.or) { builder in
         authorizedUser.branches.forEach { branch in
            guard let branchId = branch.id else { return }
               builder.filter(\.$branch.$id == branchId)
            }
         }
         .join(Customer.self, on: \BranchCustomer.$customer.$id == \Customer.$id)
         .sort(Customer.self, \.$firstName)
         .with(\.$customer)

But I'm not quite sure if this is efficient or the right way.

Thank you in advance!

Upvotes: 3

Views: 656

Answers (4)

Mitul Pokiya
Mitul Pokiya

Reputation: 182

The easiest way to filter customers by their associated branches using Vapor 4 is to utilize the join(siblings:) method along with the filter method. Here's how you can achieve this:

let filteredCustomers = try await Customer.query(on: req.db)
        .join(siblings: \.$branches)
        .filter(Branch.self, \.$id ~~ branchIds)
        .all()

Upvotes: 1

Jono Forbes
Jono Forbes

Reputation: 121

Or actually - you could be even more succinct... but note I haven't tested all possible ways of initialising a ModelValueFilter with an extension like this. I wouldn't be surprised if it introduced some unexpected behaviour.

extension QueryBuilder {
    @discardableResult
    public func filter<To: FluentKit.Model, Through: FluentKit.Model>(
        siblings: KeyPath<Model, SiblingsProperty<Model, To, Through>>,
        _ filter: ModelValueFilter<To>
    ) -> Self {
        self.join(siblings: siblings).filter(To.self, filter)
    }
}

It does make for a beautifully simple API though.

let filteredCustomers = try await Customer.query(on: request.db)
    .filter(siblings: \.$branches, \.$id ~~ branchIds)
    .all()

Upvotes: 1

Jono Forbes
Jono Forbes

Reputation: 121

If you wanted to make your query syntax a little cleaner / if you're making a lot of similar queries, you might consider adding a simple extension to Fluent's QueryBuilder class.

For example:

extension QueryBuilder {
    @discardableResult
    public func filter<
        Field: QueryableProperty,
        To: FluentKit.Model,
        Through: FluentKit.Model,
        Values: Collection<Field.Value>
    >(
        siblings: KeyPath<Model, SiblingsProperty<Model, To, Through>>,
        _ fieldKeyPath: KeyPath<To, Field>,
        subset: Values,
        inverse: Bool = false
    ) -> Self
    where Field.Model == To, Values.Element == Field.Value {
        self
            .join(siblings: siblings)
            .filter(
                .extendedPath(
                    To.path(for: fieldKeyPath),
                    schema: To.schemaOrAlias,
                    space: nil
                ),
                .subset(inverse: inverse),
                .array(subset.map { .bind($0) })
            )
    }
}

With your example (and assuming async/await), this would then be usable like so:

let filteredCustomers = try await Customer.query(on: request.db)
    .filter(siblings: \.$branches, \.$id, subset: branchIds)
    .all()

Upvotes: 2

0xTim
0xTim

Reputation: 5620

You need to use a join to bridge across from the customer table to the branch table which then allows you to return all the customers in a certain branch. See https://docs.vapor.codes/fluent/query/#join for more details

Upvotes: 1

Related Questions