Video #318: SQL Builders: Order

Episode: Video #318 Date: Mar 24, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep318-sql-builders-order

Description

We add sorting to our SQL builder, which will give us a powerful, succinct syntax for controlling the order of results. We will start small but build up to parameter packs and even a custom result builder to get the most flexibility out of our API.

Video

Cloudflare Stream video ID: b8ad63ab58cba961fc2070ea4abc8f36 Local file: video_318_sql-builders-order.mp4 *(download with --video 318)*

References

Transcript

— 0:05

We now have the basics of a query builder library implemented, and it already is more powerful than probably any Swift SQL library out there. It is capable of expressing complex selections from a table, including columns, aggregates, and even subqueries. And best of all, we are able to leverage some of Swift’s most advanced features, such as key paths, macros and variadic generics, to provide a lot of type safety and static checks that give us a lot of confidence we are writing valid SQL statements.

— 0:31

But there is still a lot more functionality in a SQL

WHERE 0:54

But for right now we are going to concentrate on ordering. Once you have performed a select you will typically have a big list of rows, and you often want to order those rows in particular manner. SQL has great support for sorting, allowing you to sort by any number of fields in any direction, and you can even perform computations in the ordering, such as sorting a text column by ignoring the case of characters.

WHERE 1:24

Let’s first see what SQL has to offer when it comes to ordering, and then see how we can recreate all of it in our query builder. Ordering in SQL

WHERE 1:37

Let’s go back to the fantastic SQLite documentation to remind ourselves of what the

SELECT 1:45

We are now familiar with a bit of this, in particular the

FROM 1:55

Let’s skip all the way down to bottom where we will see that one can optionally provide an ORDER BY clause, and its specification is given by another flow chart .

FROM 2:22

This is quite a bit simpler. This just says that after the ORDER BY clause we can list out the column or expression we want to sort by, followed by an optional collation, which can be used to order in a insensitive manner among other things, followed by an optional

NULL 2:38

Let’s give this a spin in SQLite. Suppose we wanted to select all reminders and sort them by their title: sqlite> SELECT * FROM reminders ORDER BY title; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 4 │ Buy concert tickets │ 1 │ 2 │ │ 5 │ Call accountant │ 0 │ 3 │ │ 2 │ Get haircut │ 0 │ │ │ 1 │ Groceries │ 0 │ │ │ 3 │ Take a walk │ 1 │ 1 │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 2:51

That seems to have worked nicely. We can also sort by their priority: sqlite> SELECT * FROM reminders ORDER BY priority; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 1 │ Groceries │ 0 │ │ │ 2 │ Get haircut │ 0 │ │ │ 3 │ Take a walk │ 1 │ 1 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ │ 5 │ Call accountant │ 0 │ 3 │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 3:06

By default SQLite considers

NULL 3:33

We can also sort the reminders from highest to lowest priority: sqlite> SELECT * FROM reminders ORDER BY priority DESC; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 5 │ Call accountant │ 0 │ 3 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ │ 3 │ Take a walk │ 1 │ 1 │ │ 1 │ Groceries │ 0 │ │ │ 2 │ Get haircut │ 0 │ │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 4:01

We can even sort by multiple things, such as if we wanted to first sort the reminders by their isCompleted column, and then for any reminders with the same isCompleted value we will further sort by their title: sqlite> SELECT * FROM reminders ORDER BY isCompleted, title; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 5 │ Call accountant │ 0 │ 3 │ │ 1 │ Groceries │ 0 │ │ │ 2 │ Get haircut │ 0 │ │ │ 4 │ Buy concert tickets │ 1 │ 2 │ │ 3 │ Take a walk │ 1 │ 1 │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 4:20

Here we can see that of the 3 incomplete reminders they are sorted by their title. We can even sort by isCompleted in an ascending fashion, and then by the title in a descending fashion: sqlite> SELECT * FROM reminders ORDER BY isCompleted, title DESC; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 1 │ Groceries │ 0 │ │ │ 2 │ Get haircut │ 0 │ │ │ 5 │ Call accountant │ 0 │ 3 │ │ 3 │ Take a walk │ 1 │ 1 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 4:52

We can even use expressions to sort by, like if we only wanted to sort on the length of the title of the reminder first: sqlite> SELECT * FROM reminders ORDER BY length(title); ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 1 │ Groceries │ 0 │ │ │ 2 │ Get haircut │ 0 │ │ │ 3 │ Take a walk │ 1 │ 1 │ │ 5 │ Call accountant │ 0 │ 3 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 5:16

And then for any reminders matching that condition we could further sort by isCompleted putting the completed reminders first: sqlite> SELECT * FROM reminders ORDER BY length(title), isCompleted DESC; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 1 │ Groceries │ 0 │ │ │ 3 │ Take a walk │ 1 │ 1 │ │ 2 │ Get haircut │ 0 │ │ │ 5 │ Call accountant │ 0 │ 3 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 5:28

Now we see that “Take a walk” has been sorted above “Get haircut” because although it is the same length, it is completed whereas “Get haircut” is not.

NULL 5:37

And then further, if there were any rows that had the same length of title and same isCompleted status, we could further sort them by their titles alphabetically: sqlite> SELECT * FROM reminders ORDER BY length(title), isCompleted DESC, title; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 1 │ Groceries │ 0 │ │ │ 3 │ Take a walk │ 1 │ 1 │ │ 2 │ Get haircut │ 0 │ │ │ 5 │ Call accountant │ 0 │ 3 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ └────┴─────────────────────┴─────────────┴──────────┘

NULL 5:51

This shows just how powerful sorting in SQL can be, and it’s barely scratching the surface. And so this also shows just how difficult of a job we have ahead of ourselves. We want to be able to support all of these kinds of sorts with our query builder, and even more. Ordering in our builder

NULL 6:12

We have now seen just how complex ordering can be in SQL, but it is also incredibly powerful. Honestly, I think it puts to shame any of the sorting APIs available to us in Swift. SQL ordering allows us to describe at a very high level how we want things sorted, and it just works. Stephen

NULL 6:28

But now we need to unlock this power in our query building library, and that’s going to take some work. We want to support everything we just saw, such as sorting by multiple columns, choosing the direction of sorting, the collation of sorting, putting nulls first or last, and even sorting by expressions.

NULL 6:46

It’s going to take some time to get there, so let’s dig in.

NULL 6:50

Let’s start by theorizing a syntax that we would like for sorting, and then we will make it a reality. Let’s even theorize this syntax over in a test so that once we get it compiling we can verify that it works the way we expect.

NULL 7:09

What if after getting into the entry point of query building syntax via the all() method we had an order method: Reminder.all().order

NULL 7:27

And as we saw with select , it can be powerful for these kinds of methods to take a trailing closure that is handed the schema of the table that we are dealing with: Reminder.all() .order { }

NULL 7:31

In this closure we can pluck out the column we want to sort by, like the title of the reminder: Reminder.all() .order { $0.title } And if this theoretical syntax were to compile, we could snapshot the result for the test. @Test func selectOrder() { assertInlineSnapshot() of: Reminder.all().order { $0.title }, as: .sql ) }

NULL 7:39

Further, to sort by multiple columns we would simply bundle up the columns into a tuple: @Test func selectOrderByMultipleColumns() { assertInlineSnapshot() of: Reminder.all() .order { ($0.priority, $0.title) }, as: .sql ) }

NULL 7:59

It would also be great if we could determine when to sort by ascending or descending by invoking a method on the column: @Test func selectOrderByMultipleColumnsWithDirection() { assertInlineSnapshot() of: Reminder.all() .order { ($0.priority.desc(), $0.title) }, as: .sql ) }

NULL 8:18

And even specify whether NULLs should be first or last: @Test func selectOrderByMultipleColumnsNullsFirst() { assertInlineSnapshot() of: Reminder .all() .order { ($0.priority.desc(nulls: .first), $0.title) }, as: .sql ) }

NULL 8:29

And then further, what if you could even sort by expressions, such as the length of the title column: @Test func selectOrderByExpression() { assertInlineSnapshot() of: Reminder .all() .order { ($0.title.length(), $0.isCompleted.desc()) }, as: .sql ) }

NULL 8:59

This would all be amazing, and we can make it all possible. So let’s start.

NULL 9:09

From our theoretical syntax we can see that we want the order method to be defined on the Select type, which is what all() returns. So, let’s add a method to Select : struct Select { … func order() { } }

NULL 9:21

This function will take a trailing closure as an argument, just as we did for the select method: func order<each OrderingTerm>( _ orders: (Columns) -> (repeat each OrderingTerm) ) -> Select { }

NULL 9:55

We want this trailing closure to take the columns of the schema we are dealing with as an argument so that we can decide what columns we want to sort on. However, unlike with the select method, we do not currently have access to columns. Right now the Select type has no knowledge of the underlying table that we are selecting from.

NULL 10:03

Let’s make the Select type generic over the Table that it is selecting from: struct Select<From: Table>: QueryExpression { … }

NULL 10:08

This means we no longer need to hold onto a from string property: struct Select<From: Table>: QueryExpression { // var from: String var columns: [String] … }

NULL 10:12

…because we can now derive the table name from the generic: var queryString: String { let columns = columns.isEmpty ? "*" : columns.joined(separator: ", ") return """ SELECT \(columns) FROM \(From.tableName) """ }

NULL 10:22

We now have a few compiler errors, but they are easy enough to fix. All of our helpers returning Select types now need to specify the generic, which can be Self , and we no longer need to provide a from argument when constructing a Select : extension Table { static func select( _ columns: String... ) -> Select<Self> { Select(columns: columns) } … }

NULL 10:42

So, let’s go back the order method, but now we can take the Columns from the From generic: func order<each OrderingTerm: QueryExpression>( _ orders: (From.Columns) -> (repeat each OrderingTerm) ) -> Select { }

NULL 10:45

The implementation will be quite similar to what we did for select , where we start by invoking the trailing closure on our table’s columns in order to get all the order expressions specified by the user: let orders = orders(From.columns)

NULL 10:59

And then we can iterate over this variadically sized tuple of orders to render the query expressions out to a SQL string: var orderStrings: [String] = [] for order in repeat each orders { orderStrings.append(order.queryString) }

NULL 11:20

And then finally we have to return a Select value: return Select( columns: <#[String]#> )

NULL 11:27

But then the question is how do we keep track of these orders in the Select ? Currently the Select type is only tracking what columns we are selecting from. Sounds like we need to hold onto more information in the Select type: struct Select<From: Table>: QueryExpression { var columns: [String] var orders: [String] = [] … }

NULL 11:42

And now we can pass along the newly computed orders to Select : return Select(columns: columns, orders: orderStrings)

NULL 11:56

That’s all it takes to implement the order method on the

SELECT 12:36

And just like that the library is compiling, but our tests are not. The snapshot helper we wrote earlier for Select is missing its generic: extension Snapshotting where Value == Select, Format == String { Snapshotting<String, String>.lines .pullback(\.queryString) }

SELECT 12:50

But it’s also needlessly specific on Select when we make a small change to generalize to work with all query expressions: extension Snapshotting where Value: QueryExpression, Format == String { Snapshotting<String, String>.lines .pullback(\.queryString) }

SELECT 13:21

2 of the 5 tests we wrote a moment ago are also compiling. Let’s comment out the 3 that are not yet in working order.

SELECT 13:31

…and if we run the test suite we will see our generated snapshots are all looking really good! Even something as complicated as ordering by multiple columns is working: @Test func selectOrderByMultipleColumns() { assertInlineSnapshot( of: Reminder.all().order { ($0.priority, $0.title) }, as: .sql ) { """ SELECT * FROM reminders ORDER BY priority, name """ } } That’s pretty amazing, and it didn’t take much work. Advanced ordering

SELECT 13:44

We now have the basics of ordering in our SQL builder mini library. You simply use the order method, provide a trailing closure, and then you can describe any number of columns that you want to order by bundling them up in a tuple. And it’s pretty amazing to see how Swift’s variadic generics is able to handle this so easily. Brandon

SELECT 14:06

But right now we are only allowed to sort each column in an ascending fashion. What if we want to sort some columns by descending? That’s going to take a little bit of extra work, so let’s dig in.

SELECT 14:20

Let’s look at one of the tests we wrote earlier that is not currently compiling: @Test func selectOrderByMultipleColumnsWithDirection() { assertInlineSnapshot( of: Reminder.all().order { ($0.priority.desc(), $0.title) }, as: .sql ) }

SELECT 14:24

This wants to be able to sort by the reminder’s priority in a descending fashion, and then sort by the reminder’s title. Currently this desc() function is not defined, and so let’s do it now.

SELECT 14:37

We will approach this how we approached the aggregate functions defined on columns, such as count() , avg() and group_concat() . We will extend the Column type and add some methods to it: extension Column { func asc() -> some QueryExpression { } func desc() -> some QueryExpression { } }

SELECT 15:04

In each of these methods we will return a concrete type that conforms to the QueryExpression protocol and knows how to render just the tiny SQL fragment of suffixing the column name with

DESC 15:35

And now we can define this concrete OrderClause type just as we did with the aggregate functions: struct OrderingTerm: QueryExpression { let isAscending: Bool let column: Column let direction = isAscending ? " ASC" : " DESC" var queryString: String { "\(column.queryString)\(direction)" } }

DESC 16:12

With that done our test suite is now compiling, and let’s see what SQL our test generates: @Test func selectOrderByMultipleColumnsWithDirection() { assertInlineSnapshot( of: Reminder.all().order { ($0.priority.desc(), $0.title) }, as: .sql ) { """ SELECT * FROM reminders ORDER BY priority DESC, name """ } }

DESC 16:22

And that’s pretty incredible.

DESC 16:28

And while we’re here, let’s go ahead and support ordering

NULL 16:42

Let’s make this syntax a reality. We will add an argument to the asc and desc methods to support this: func asc(nulls: ) -> some QueryExpression { OrderingTerm(isAscending: true, column: self) } func desc(nulls: ) -> some QueryExpression { OrderingTerm(isAscending: false, column: self) }

NULL 16:51

We have two options here, so let’s introduce an enum for each case: enum NullOrder: String { case first = "FIRST" case last = "LAST" } … func asc( nulls: NullOrder? = nil ) -> some QueryExpression { OrderingTerm(isAscending: true, column: self) } func desc( nulls: NullOrder? = nil ) -> some QueryExpression { OrderingTerm(isAscending: false, column: self) }

NULL 17:23

And we will need to pass this information along to OrderClause so that it can know how to render the query fragment: func asc(n ulls nullOrder: NullOrder? = nil ) -> some QueryExpression { OrderingTerm( isAscending: true, nullOrder: nullOrder, column: self ) } func desc( nulls nullOrder: NullOrder? = nil ) -> some QueryExpression { OrderingTerm( isAscending: false, nullOrder: nullOrder, column: self ) }

NULL 17:29

And now we can update OrderFunction to take into account this extra information: struct OrderingTerm: QueryExpression { let isAscending: Bool let nullOrder: NullOrdering? let column: Column let direction = isAscending ? " ASC" : " DESC" var queryString: String { var sql = "\(column.queryString)\(direction)" if let nullOrder { sql.append(" NULLS \(nullOrder.rawValue)") } return sql } }

NULL 18:22

And now the test suite is compiling and the test that orders

NULL 22:12

With just a little bit of work we are able to support ordering in a descending an ascending fashion, and we are even able to customize how

NULL 22:30

At the end of the day we think it’s best for you to be familiar with SQL, and you should know how it things work like NULLs , but it’s not necessary to memorize every little bit of syntax precisely. We want the library to make all the available syntax discoverable and easy for you to piece together. Stephen

NULL 22:53

So ordering in our SQL builder is now looking pretty nice, but there is still some functionality that is not available to us. As we saw earlier, it’s possible to order by expressions, such as the length of a text column, or ordering in a case-insensitive manner.

NULL 23:05

Let’s see what it takes to support those advanced kinds of orderings.

NULL 23:11

We have written one test that isn’t yet in compiling shape: @Test func selectOrderByExpression() { assertInlineSnapshot( of: Reminder .all() .order { ($0.title.length(), $0.isCompleted.desc() }, as: .sql ) }

NULL 23:22

…but currently this does not compile. There is no length() function defined, but there is nothing stopping us from defining it.

NULL 23:27

Just as we did with the avg and group_concat SQL functions, we will extend the Column type and add a length function: extension Column { func length() -> some QueryExpression { LengthFunction(column: self) } }

NULL 23:46

And then we can define this LengthFunction type as a concrete conformance to QueryExpression that invokes the SQL length function on the column: struct LengthFunction: QueryExpression { let column: Column var queryString: String { "length(\(column.queryString))" } }

NULL 24:04

And amazingly, that is all it takes. Our test suite now compiles and generates a SQL snapshot. @Test func selectOrderByExpressionDescending() { assertSnapshot( of: Reminder .all() .order { ($0.title.length(), $0.isCompleted.desc()) }, as: .sql ) { """ SELECT * FROM reminders ORDER BY length(title), isCompleted DESC """ } }

NULL 24:24

However, there is one thing not quite right with what we have. It does not allow us to apply multiple functions. For example, what if we wanted to sort by the length of the title in a descending fashion: $0.title.length().desc() Value of type ‘some QueryExpression’ has no member ‘desc’

NULL 24:34

But this does not compile because desc() is only defined on Column , and length() does not return a column. It returns some QueryExpression .

NULL 24:51

All we are seeing here is that our helpers are defined on a needlessly concrete type, which is the Column type. We should broaden their definition to be on QueryExpression , that way you can chain them together.

NULL 25:02

We might hope we could simply move all of the methods to QueryExpression like so: extension QueryExpression { func count( distinct: Bool = false ) -> some QueryExpression { … } func avg() -> some QueryExpression { … } func length() -> some QueryExpression { … } func groupConcat( separator: String? = nil ) -> some QueryExpression { … } func asc(nulls: NullOrder? = nil) -> some QueryExpression { … } func desc(nulls: NullOrder? = nil) -> some QueryExpression { … } }

NULL 25:09

…but none of these compile now because all of the concrete types hold onto a concrete Column type and we now need to pass along a QueryExpression : CountFunction(isDistinct: distinct, column: self)

NULL 25:16

There are two ways to fix this. The simplest would be to update the concrete types to hold onto a base any QueryExpression : struct CountFunction: QueryExpression { let isDistinct: Bool let base: any QueryExpression var queryString: String { let distinct = isDistinct ? "DISTINCT " : "" return "count(\(distinct)\(base.queryString))" } }

NULL 25:26

And then when constructing the concrete type we pass along the base: CountFunction(isDistinct: distinct, base: self)

NULL 25:33

That gets this one usage compiling. However, type erasure like this: let base: any QueryExpression …does come at a slight cost. The cost can be very tiny, measured in microseconds, but when called thousands of times it can add up. And the way to fix this is to preserve the static type information for as long as possible, which means introducing a generic to CountFunction : struct CountFunction< Base: QueryExpression >: QueryExpression { let isDistinct: Bool let base: Base var queryString: String { let distinct = isDistinct ? "DISTINCT " : "" return "count(\(distinct)\(base.queryString))" } }

NULL 26:02

This compiles and avoids the overhead of the existential.

NULL 26:07

Let’s update all of our concrete types to follow this same pattern.

NULL 26:27

Now everything compiles, even tests, and the test suite passes after generating a valid snapshot. The test where we are ordering by the length of the reminder’s title in a descending fashion is generating output we expect: @Test func selectOrderByExpressionDescending() { assertInlineSnapshot( of: Reminder .all() .order { $0.title.length().desc(), $0.isCompleted.desc() }, as: .sql ) { """ SELECT * FROM reminders ORDER BY length(title) DESC, isCompleted DESC """ } }

NULL 26:44

And that is pretty incredible.

NULL 26:50

And it’s so easy to make these functions, let’s add another. Let’s make it so that we can order by the reminders titles, but in a case-insensitive manner. The way one does that is to suffix the name of the column with COLLATE NOCASE , and we might want to get access to this functionality in our builder like so: @Test func selectOrderByExpressionCollation() { assertInlineSnapshot( of: Reminder .all() .order { $0.title.collate(.nocase).desc() }, as: .sql ) }

NULL 27:16

This doesn’t compile right now, but it’s straightforward to add this collate function.

NULL 27:18

We extend QueryExpression with the collate method and a theoretical Collation type: extension QueryExpression { func collate( _ collation: Collation ) -> some QueryExpression { Collate(collation: collation, base: self) } }

NULL 27:43

And we can take theory to practice by defining the type: enum Collation: String { case nocase = "NOCASE" case binary = "BINARY" case rtrim = "RTRIM" }

NULL 28:01

Then we can define a generic, concrete type that describes how to apply a collation to any base query expression: struct Collate<Base: QueryExpression>: QueryExpression { let collation: Collation let base: Base var queryString: String { "\(base.queryString) COLLATE \(collation.rawValue)" } }

NULL 28:44

That is all it takes and our test suite is now compiling and the whole suite passes after generating a valid snapshot. @Test func selectOrderByExpressionCollation() { assertInlineSnapshot( of: Reminder .all() .order { $0.title.collate(.nocase).desc() }, as: .sql ) { """ SELECT * FROM reminders ORDER BY title COLLATE NOCASE DESC """ } } Ordering result builder

NULL 29:08

We can now order by expressions in our queries, including things like ordering by the length of a text column, or ordering a text column in a case-insensitive manner using collation. Brandon

NULL 29:18

And this is all great, and we could just stop here and move onto the next topic for SQL building, which is going to be

WHERE 29:42

Let’s see how we can make this kind of common task a bit nicer to deal with using our query builder.

WHERE 29:49

We are going to begin, as we have done a number of times on this series, by first writing down an ideal syntax for what we want to accomplish. And then we will make that syntax a reality.

WHERE 29:58

Let’s start a new test: @Test func complexOrderBy() { }

WHERE 30:04

And imagine there is some condition that determines how we sort in our query: let shouldSortByTitle = true

WHERE 30:15

We would love if we could simply check this condition right in the order trailing closure so that we can determine whether or not to order the query: assertInlineSnapshot( of: Reminder.all().order { if shouldSortByTitle { $0.title.collate(.nocase).desc() } }, as: .sql )

WHERE 30:57

This technically compiles right now, but produces a warning: Result of call to ‘desc(nulls:)’ is unused

WHERE 31:01

This warning is letting us know that something isn’t quite right about what we are doing here. Because we are performing multi-line logic in this closure, Swift infers the return type to be Void , which is equivalent to an empty tuple. An empty tuple is a perfectly fine thing to be inferred for a function using variadic generics, as our trailing closure does: _ orders: (From.Columns) -> (repeat each OrderingTerm)

WHERE 31:47

And so the query builder thinks we are just not sorting on any columns.

WHERE 31:54

And we can confirm this by running the test to see what SQL is generated: let shouldSortByTitle = true assertInlineSnapshot( of: Reminder.all().order { if shouldSortByTitle { $0.title.collate(.nocase).desc() } }, as: .sql ) { """ SELECT * FROM reminders """ }

WHERE 32:01

And indeed, no ORDER BY clause was generated.

WHERE 32:07

We could try to specify an explicit return: if shouldSortByTitle { return $0.title.collate(.nocase).desc() }

WHERE 32:11

…but that just causes compiler errors because now we need to specify a return statement in the else branch. And we don’t want to do that right now. We would like for no ORDER BY clause to be specified when the condition is false.

WHERE 32:44

This is a situation where result builders really shine. They allow us to use a simple Swift syntax to describe how to build up a complex result, which in this case is building up a collection of ordering terms. Once implemented we will be able to use simple logic statements to determine which orders to apply, including if statements, if / else statements, and even switch statements.

WHERE 33:15

Let’s start implementing a builder to give us access to this syntax. First we will define a new order method that takes a trailing closure that is marked with a theoretical @OrderBuilder that we have yet to define: func order( @OrderBuilder _ orders: (From.Columns) -> <#???#> ) -> Select { } And the question is: what kind of value is this builder going to build?

WHERE 33:49

Looking at our existing order method we see that the trailing closure uses fancy variadic generics to capture all of the type information for ordering by multiple columns and expressions” func order<each OrderingTerm: QueryExpression>( _ orders: (From.Columns) -> (repeat each OrderingTerm) ) -> Select { …but right in the implementation we are just turning all of that type information into a collection of strings to pass on to the Select type: func order<each OrderingTerm: QueryExpression>( _ orders: (From.Columns) -> (repeat each OrderingTerm) ) -> Select { let orders = orders(From.columns) var orderStrings: [String] = [] for order in repeat each orders { orderStrings.append(order.queryString) } return Select( columns: columns, orders: self.orders + orderStrings ) }

WHERE 34:02

Since the final result being built is just a collection of strings, that is what our builder will be focused on building up: @OrderBuilder _ orders: (From.Columns) -> [String]

WHERE 0:00

And if that is the type that we will be building, then that makes it easy to implement this new order method: func order( @OrderBuilder _ orders: (From.Columns) -> [String] ) -> Select { Select( columns: columns, orders: self.orders + orders(From.columns) ) }

WHERE 34:31

Now let’s define a @resultBuilder type, which can just be an enum since it only has static requirements: @resultBuilder enum OrderBuilder { }

WHERE 35:03

At the very least we need to implement a buildBlock method: static func buildBlock( _ components: <#Component#>... ) -> <#Component#> { <#code#> }

WHERE 35:06

As as we just mentioned, this builder is going to be focused on building up a collection of strings that represent the SQL fragments to order the query by: static func buildBlock( _ components: [String] ) -> [String] { components }

WHERE 37:05

That’s all it takes to get things compiling, but it is not yet correct.

WHERE 37:33

Our query is not choosing the overload of order that uses the builder: Reminder.all().order { if shouldSortByTitle { $0.title.collate(.nocase).desc() } }

WHERE 37:35

This still has a warning: Warning Result of call to ‘desc(nulls )’ is unused

WHERE 37:39

To force the compiler to use our builder method, let’s add an explicit argument label we can use to distinguish it: func order( @OrderBuilder build orders: (From.Columns) -> [String] ) -> Select { … }

WHERE 37:59

And we can update our test to use this explicit label: Reminder.all().order(build: { if shouldSortByTitle { $0.title.collate(.nocase).desc() } }) Closure containing control flow statement cannot be used with result builder ’OrderBuilder

WHERE 38:20

This is happening because we have a logical statement in the trailing closure, and in order for Swift to be able to translate that in our builder, we must at least implement the buildOptional static method: static func buildOptional( _ components: <#Component#>? ) -> <#Component#> { <#code#> }

WHERE 38:39

This is invoked when Swift sees an if statement in a builder context with no corresponding else . When the condition of the if is true, it builds the result inside the if and passes it to this buildOptional . And when the condition is false it passes nil .

WHERE 38:56

The implementation is quite straightforward: static func buildOptional( _ component: [String]? ) -> [String] { component ?? [] } When the condition is true we take everything build by the body of the if , and otherwise we just build an empty array.

WHERE 39:20

The library is compiling, but back in our test we have a new error. Cannot convert value of type ‘some QueryExpression’ to expected argument type '[String]'

WHERE 39:26

And that’s because the result builder we have defined so far only speaks arrays of strings. We need one additional step that can take each statement in the builder context and transform it into the result that the builder understands.

WHERE 39:54

The way one does this is via a buildExpression static function in the result builder: static func buildExpression( _ expression: <#Expression#>... ) -> <#Component#> { <#code#> }

WHERE 40:01

When this is implemented you can imagine it surrounding each statement in your builder closure, allowing it to convert the values in the closure to a common value that the builder understands.

WHERE 40:47

In our case, this is where it is appropriate to bring back the variadic generics for turning a tuple of query expression into an array of strings, just as we are doing in the first order method we defined: static func buildExpression< each OrderingTerm: QueryExpression >( _ expression: (repeat each OrderingTerm) ) -> [String] { var orderStrings: [String] = [] for order in repeat each expression { orderStrings.append(order.queryString) } return orderStrings }

WHERE 41:50

And now, finally , our test is building without any warnings, and is actually using the order builder. If we run the test we will see it re-records with what we expect: @Test func complexOrderBy() { let shouldSortByTitle = true assertInlineSnapshot( of: Reminder.all().order { if shouldSortByTitle { $0.title.collate(.nocase).desc() } }, as: .sql ) { """ SELECT * FROM reminders ORDER BY title COLLATE NOCASE DESC """ } } The query is ordering by title, collated with

NOCASE 42:15

And if we copy-paste this test, change the condition to false, and run the test: @Test func orderByFalseCondition() { let shouldSortByTitle = false assertInlineSnapshot( of: Reminder.all().order { if shouldSortByTitle { $0.title.collate(.nocase).desc() } }, as: .sql ) { """ SELECT * FROM reminders """ } }

NOCASE 42:27

…we will see that the ORDER BY clause is left off. That means we can perform little bits of logic in the order trailing closure to determine how to order our results, and the library will always produce a valid SQL query.

NOCASE 42:50

However, we’re not quite done yet. Let’s copy-and-paste the test one more time and introduce an else statement: @Test func orderByElseCondition() { let shouldSortByTitle = false assertInlineSnapshot( of: Reminder.all().order { if shouldSortByTitle { $0.title.collate(.nocase).desc() } else { $0.isCompleted } }, as: .sql ) }

NOCASE 43:09

Now we are back to compiler errors: Closure containing control flow statement cannot be used with result builder ‘OrderBuilder’ Which is letting us know that Swift does not know how to build our result when the condition is false.

NOCASE 43:30

To support this we just need to implement the buildEither(first:) and buildEither(second:) static methods: static func buildEither( first component: [String] ) -> [String] { component } static func buildEither( second component: [String] ) -> [String] { component }

NOCASE 43:40

Now when Swift sees an if / else if / else statement in a builder context, it will call one of these methods depending on if the condition is true or not.

NOCASE 43:58

Now everything is compiling with no warnings, and running the test we see it generates the query we expect: @Test func orderByElseCondition() { let shouldSortByTitle = false assertInlineSnapshot( of: Reminder.all().order { if shouldSortByTitle { $0.title.collate(.nocase).desc() } else { $0.isCompleted } }, as: .sql ) { """ SELECT * FROM reminders ORDER BY isCompleted """ } } Because the condition is false it has built the result specified by $0.isCompleted and so generated an ORDER BY clause for completed reminders.

NOCASE 44:09

But because we have implemented buildExpression , we can also immediately return different variadics from each branch: @Test func orderByElseCondition() { let shouldSortByTitle = false assertInlineSnapshot( of: Reminder.all().order { if shouldSortByTitle { ($0.title.collate(.nocase).desc(), $0.priority) } else { $0.isCompleted } }, as: .sql ) { """ SELECT * FROM reminders ORDER BY isCompleted """ } } And everything compiles just fine.

NOCASE 45:14

And because we have implemented the buildEither static methods, we also instantly get access to switching over enums in a builder context: @Test func orderBySwitch() { enum Order { case title, priority } let order = Order.title assertInlineSnapshot( of: Reminder.all().order { switch order { case .title: ( $0.title.collate(.nocase).desc(), $0.isCompleted ) case .priority: $0.priority.desc() } }, as: .sql ) { """ SELECT * FROM reminders ORDER BY title COLLATE NOCASE DESC, isCompleted """ } } Next time: filtering Brandon

NOCASE 46:41

We have now created a type-safe Swift API for constructing a small subset of the SQL language. We have built tools that allow us to get a static description of the tables in our SQLite database, and that has unlocked all kinds of cool things:

NOCASE 46:56

We can write

SELECT 47:07

We can compute aggregates of table columns, such as counts, averages, concatenations of strings, and more.

SELECT 47:16

And now we can order the rows returned by sorting on any number of columns, in any direction, and we can even sort by expressions such as the length of a string column. Stephen

SELECT 47:28

But so far we have not tackled what might be the most important of the SQL language, and might be seen as the most complex part when trying to create a type-safe API for SQL. And that is the

WHERE 47:40

This is what lets you filter out the rows that are returned from the database. For example, we may want to only fetch the incomplete reminders. Or maybe we just want the high priority reminders. Or maybe we want to search the names of the reminders for a particular substring. Or maybe we want to combine a bunch of these things into one gigantic predicate that is run on every single row of the reminders table.

WHERE 48:02

It is possible to support

WHERE 48:21

Let’s start by getting familiar with what one can do in a