Vapor - Group Filter

When you need to pick out a small subset of data directly from your database, you would write SQL queries to filter using the "WHERE" clause with its matching conditions to pick out the data you need for your specific requirements.

Similary, while writing your Fluent queries in Vapor, you would generally add more restrictive filter conditions one after the other onto your query which would match the data that satisfies all these conditions and return them to you. This is becuase the default behaviour of chaining multiple filter queries is equivalent to using the and condition on them.

Let's say we wanted to find a box for an item. For this we will try and find all boxes whose length is greater than that of the item and whose breadth is greater than that of the item as so using a Fluent query:

Box.query(on: database)
    .filter(\Box.$length > item.length)
    .filter(\Box.$breadth > item.breadth)
    .all()

But there may be other boxes present which can also fit your item but haven't shown up in your result as their length and breadth were interchanged while entering their data. So that would be all boxes whose length is greater than that of the breadth of the item and whose breadth is greater than that of the length of the item:

Box.query(on: database)
    .filter(\Box.$length > item.breadth)
    .filter(\Box.$breadth > item.length)
    .all()

Now, if we wanted to achieve the same functionality using a single query, how would we combine these two conditions?

This is where the group filter comes into play.

We simply group the separate conditions together and use them in either and or or groups as shown below:

Box.query(on: database)
    .group(.or) { orGroup in
        orGroup.group(.and) { group1 in
            group1
            .filter(\.$length >= item.length)
            .filter(\.$breadth >= item.breadth)
        }
        orGroup.group(.and) { group2 in
            group2
            .filter(\.$length >= item.breadth)
            .filter(\.$breadth >= item.length)
        }
    }
    .all()

Let's break this down to understand it better.

We want the boxes that match either the first group (group1) or the second group (group2) so we specify the group containing these groups (orGroup) with .or This needs to be specified explicitly as they would be used as and by default

Next, we segregated our filter conditions into two separate sub-groups, group1 and group2.
Since we wanted the length and breadth to be checked (as before) we specify the group containing them with .and.
This sub-grouping with and logic is needed as chained filters within an or group will use the or logic when combining filter conditions


Reference: Vapor Docs -> Fluent -> Query