Video #321: SQL Builders: Joins in Swift

Episode: Video #321 Date: Apr 14, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep321-sql-builders-joins-in-swift

Description

It’s time to support one of the most complicated parts of SQL in our query building library: joins. We will design an API that is simple to use but leverages some seriously advanced language features, including type-level parameter packs.

Video

Cloudflare Stream video ID: df7e1d6981e9e0bec2ccbc36b669cd58 Local file: video_321_sql-builders-joins-in-swift.mp4 *(download with --video 321)*

References

Transcript

— 0:05

We have now completed a deep dive into SQL joins. We didn’t cover absolutely everything there is to know about them, but we did cover a lot:

— 0:13

We now know how to join two tables together in a query, such as selecting all reminders along with the title of its associated list. Stephen

— 0:21

We also know how to aggregate across joins of tables. For example, we were able to select the count of all reminders in each list as just a single query. SQL is great at aggregating data like this across many tables and rows. Brandon

— 0:35

And finally, we dipped our toes in some more advanced queries, such as counting the number of incomplete reminders for each list, which required us to aggregate across a conditional expression and to use a left join instead of a regular join.

— 0:50

This is all really powerful stuff, but we also just had an entire episode without writing a single line of Swift code. I think that’s a first for Point-Free! Stephen

— 0:58

So now it is time to start updating our query builder to support joins. We want a simple API for joining two tables together along some constraint, and then some ability to select columns from either table, or perform predicate logic with the columns of each table, as well as ordering and aggregating across the two tables.

— 1:15

Let’s dig in. Building Joins

— 1:18

Before we can explore any APIs for joining tables together we need to add a new table to our tests. Currently we have a Reminders type, so let’s also add a RemindersList type: struct RemindersList { let id: Int var title = "" }

— 1:35

And remember that in the actual version of this query building library there will be a @Table macro: // @Table struct RemindersList: Table { let id: Int var title = "" }

— 1:47

…that generates all of the column boilerplate for us. But we don’t want to waste time building that macro in these episodes. Instead we will write the boilerplate ourselves: struct RemindersList: Table { // ----- struct Columns { let id = Column<Int>(name: "id") let title = Column<String>(name: "title") } static let columns = Columns() static let tableName = "remindersLists" // ----- let id: Int var title = "" }

— 1:54

And we will also add a foreign key to our Reminder table: struct Reminder: Table { … var remindersListID: Int }

— 2:07

Which means also adding a column to the Columns type: struct Reminder: Table { struct Columns { … let remindersListID = Column<Int>(name: "remindersListID") } … }

— 2:20

We could even make this read a bit nicer if we make RemindersList identifiable: struct RemindersList: Identifiable, Table { … }

— 2:34

…because then we can refer to that ID more clearly in the foreign key relationship: struct Reminder: Table { … var remindersListID: RemindersList.ID }

— 2:38

And before even exploring the ideal syntax for joins, let’s quickly write a query to show that all of our query building tools immediately work for this new RemindersList type: @Test func selectRemindersLists() { assertInlineSnapshot( of: RemindersList.all(), as: .sql ) { """ SELECT * FROM remindersLists """ } }

— 3:18

This shows that once we do have the macro available to use we will be able to add new tables very quickly and start immediately writing queries.

— 3:24

Let’s now theorize how we want join syntax to work. As we’ve seen over and over again with select , order and most recently where , it can be incredibly powerful to use trailing closure syntax to specify query fragments.

— 3:38

So, what if after entering query building syntax with the all() static method: RemindersList .all()

— 3:44

…we could further chain on a join method that took the table we wanted to join to: RemindersList .all() .join(Reminder.self)

— 3:54

This describes the tables to join together, but doesn’t describe the constraint we want to use to join. To do that we would hope we could open up a trialing closure that is handed two arguments, one for the columns of the left side and one for the columns of the right side: RemindersList .all() .join(Reminder.self) { $0, $1 }

— 4:09

And then we could say that we want the ID of the left side to be equal to the foreign key remindersListID on the right side: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID }

— 4:20

This is exactly how we would want to write this kind of query. It gives us static access to the schema of each table, and we get to perform any logic we want in the constraint of the join.

— 4:28

So, let’s snapshot this query in order to test it: @Test func join() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID }, as: .sql ) }

— 4:36

But of course it does not yet compile because we haven’t implemented the join method yet.

— 4:42

Let’s hop over to the library code of our query builder and add a stub for the join method. The method is going to exist on the Select type, right alongside all of our other methods, such as order and where , and it will return a whole new Select value: func join( ) -> Select { }

— 5:01

The method will take the metatype of the table we want to join to: func join<Other: Table>( _ other: Other.Type ) -> Select { }

— 5:14

As well as a trailing closure that takes the columns from the first table and the join table and returns some boolean query expression: func join<Other: Table>( _ other: Other.Type, on constraint: (From.Columns, Other.Columns) -> some QueryExpression<Bool> ) -> Select { }

— 5:33

And we can implement this method by construct a new Select value to return, and to do that we will pass along the current columns, orders and where clauses: func join<Join: Table>( _ other: Join.Type, on constraint: (From.Columns, Join.Columns) -> some QueryExpression<Bool> ) -> Select { Select(columns: columns, orders: orders, wheres: wheres) }

— 5:48

This does get things compiling, even the tests. But of course it doesn’t actually do anything yet. We haven’t used the Join type or constraint trailing closure at all.

— 6:00

We need to add extra state to the Select type that holds the join clauses that have been specified: struct Select<From: Table>: QueryExpression { … var joins: [String] = [] … }

— 6:14

And then we need to update every operator we have implemented so far to pass along the current joins state when constructing a new Select value.

— 6:26

Now that we have joins state we can properly implement the join method. We will take the existing joins and append to it a new join to the other table with the specified

ON 7:09

And finally we will update the queryString property of Select to append all of these joins after the “FROM” clause: if !joins.isEmpty { sql.append("\n\(joins.joined(separator: "\n"))") }

ON 7:31

And just like that everything is compiling, and our test generates a pretty reasonable query: @Test func basicJoin() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID }, as: .sql ) { """ SELECT * FROM remindersLists JOIN reminders ON (id = remindersListID) """ } }

ON 8:01

It’s pretty incredible how easy it was to get the basics of joins into place. And if we ever did something nonsensical in the join, say in the constraint we checked that the ID of the list is equal to the title of the reminder: RemindersList .all() .join(Reminder.self) { $0.id == $1.title } Binary operator ‘==’ cannot be applied to operands of type ‘ Column<Int> ’ and ‘ Column<String> ’ This of course will fail to compile. And so our query builder has our back to make sure we have a light safety net when it comes to writing queries.

ON 8:27

Even cooler, with a bit of extra work we could integrate our Tagged library into these tools. Remember that Tagged allows us to create distinct types for identifiers of types. Currently the Reminder and RemindersList types have an ID associated type because they are both Identifiable : Reminder.ID RemindersList.ID

ON 8:53

But technically those IDs are just plain integers. That means we are allowed to do other non-sensical things like: RemindersList .all() .join(Reminder.self) { $0.id == $1.id }

ON 9:02

This query compiles just fine, but there is a 99.99% chance that this is not what you actually want. And so our Tagged library allows us to upgrade these ID types to be completely distinct types, which would make this: $0.id == $1.id …a compile-time error, making foreign key relationships even stronger.

ON 0:00

We now have the basics of joins in place, and output that looks reasonable, but let’s see what happens when we try to execute it in the console: sqlite> SELECT * ...> FROM remindersLists ...> JOIN reminders ON (id = remindersListID); Parse error: ambiguous column name: id

ON 9:29

This unfortunately produces an error. The problem is that a bare, unqualified “id” column in the “ON” clause can be interpreted to mean the “id” column on the “reminders” table or the “remindersLists” table. Since SQLite doesn’t know which one you mean, it has no choice but to emit an error.

ON 9:44

Had we written the query like this instead: sqlite> SELECT * ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = remindersListID); ┌────┬──────────┬────┬─────────────────────┬─────────────┬──────────┬─────────────────┐ │ id │ title │ id │ title │ isCompleted │ priority │ remindersListID │ ├────┼──────────┼────┼─────────────────────┼─────────────┼──────────┼─────────────────┤ │ 3 │ Family │ 1 │ Groceries │ 0 │ │ 3 │ │ 1 │ Personal │ 2 │ Get haircut │ 0 │ │ 1 │ │ 1 │ Personal │ 3 │ Take a walk │ 1 │ 1 │ 1 │ │ 3 │ Family │ 4 │ Buy concert tickets │ 1 │ 2 │ 3 │ │ 2 │ Work │ 5 │ Call accountant │ 0 │ 3 │ 2 │ │ 1 │ Personal │ 6 │ Walk the dog │ 0 │ │ 1 │ └────┴──────────┴────┴─────────────────────┴─────────────┴──────────┴─────────────────┘ …it would have worked.

ON 9:53

So, it seems like we need to force the qualification of the table name when printing columns in our queries. And luckily it’s quite straightforward. We already have a Column type that describes the columns of a schema, including how to print it to a query string: struct Column<QueryValue>: QueryExpression { var name: String var queryString: String { name } }

ON 10:06

What if we further enhanced this to also know about the table the column is being selected from so that we could qualify the column name with the table name: struct Column<QueryValue>: QueryExpression { var name: String var table: String var queryString: String { "\(table).\(name)" } }

ON 10:17

With that change the library is already compiling. However, tests are not compiling because we have constructed a bunch of Column values without specifying the table field. Unfortunately we do have to update all of this code ourselves, manually. But this is exactly the code that will be generated by a macro in the future, and so typically we will not have to worry about these kinds of things.

ON 10:25

But let’s go ahead and do it quickly for the Reminder type: struct Columns { let id = Column<Int>(name: "id", table: Reminder.tableName) let title = Column<String>(name: "title", table: Reminder.tableName) let isCompleted = Column<Bool>(name: "isCompleted", table: Reminder.tableName) let priority = Column<Int?>(name: "priority", table: Reminder.tableName) let remindersListID = Column<Int>(name: "remindersListID", table: Reminder.tableName) }

ON 10:35

…and the RemindersList type: struct Columns { let id = Column<Int>(name: "id", table: RemindersList.tableName) let title = Column<String>(name: "title", table: RemindersList.tableName) }

ON 10:41

With that one small change our test is not generate valid SQL code: @Test func basicJoin() { assertInlineSnapshot( of: RemindersList.all().join(Reminder.self) { $0.id == $1.remindersListID }, as: .sql ) { """ SELECT * FROM remindersLists JOIN reminders ON (remindersLists.id = reminders.remindersListID) """ } }

ON 10:54

All columns are now qualified with the table name, and if we copy-and-paste the query into the SQL console we will see that it works: sqlite> SELECT * ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = reminders.remindersListID); ┌────┬──────────┬────┬─────────────────────┬─────────────┬──────────┬─────────────────┐ │ id │ title │ id │ title │ isCompleted │ priority │ remindersListID │ ├────┼──────────┼────┼─────────────────────┼─────────────┼──────────┼─────────────────┤ │ 3 │ Family │ 1 │ Groceries │ 0 │ │ 3 │ │ 1 │ Personal │ 2 │ Get haircut │ 0 │ │ 1 │ │ 1 │ Personal │ 3 │ Take a walk │ 1 │ 1 │ 1 │ │ 3 │ Family │ 4 │ Buy concert tickets │ 1 │ 2 │ 3 │ │ 2 │ Work │ 5 │ Call accountant │ 0 │ 3 │ 2 │ │ 1 │ Personal │ 6 │ Walk the dog │ 0 │ │ 1 │ └────┴──────────┴────┴─────────────────────┴─────────────┴──────────┴─────────────────┘

ON 10:59

This is all looking good, but we can also show off a really cool feature of our test suite. Because we have changed how a fundamental unit of our queries is rendered, in particular the qualification of every column name with the table name, we technically have broken all of our tests. Every single test needs to be updated to qualify the table name.

ON 11:18

However, since we are using our powerful InlineSnapshotTesting library, and because the full suite is put into the record mode of recording fresh snapshots when failures are detected. @MainActor @Suite(.snapshots(record: .failed)) struct QueryBuilderTests { … }

ON 11:33

…we can simply run the test suite and fresh snapshots will be generated for us:

ON 11:44

That has immediately updated dozens of tests in our suite, and we now go one-by-one to double check that they are correct. And if I run the suite again we will see everything passes. Better joins

ON 12:06

We now have the basics of join clauses in our query builder. We can use the join method, specify the table we want to join to, and then describe the join constraint in the manner we have come to really like, which is a trailing closure that is given access to the schema of all the tables involved and that returns some query expression of a boolean value. Brandon

ON 12:25

It’s looking really great, but there is still something not quite right about what we have done so far. It turns out that the way we have modeled joins right now does not play nicely if we want to further specify

WHERE 12:41

Let’s see how we can fix this.

WHERE 13:06

Let’s write a quick test to show why this is a problem. Suppose we want to select all reminders and their associated list, but filter out the completed reminders. We would hope we could simply tack on a where clause after the join : RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .where { }

WHERE 13:37

But since the Select type has no knowledge of the table we joined to, the trailing closure only has access to the columns of the RemindersList . It doesn’t know anything about Reminder .

WHERE 14:00

Ideally where should be passed two arguments, the columns of the left side table and the columns of the right side table: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .where { $0, $1 }

WHERE 14:09

And the right side table should be the “reminders” table, allowing us to filter by incomplete reminders: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .where { !$1.isCompleted }

WHERE 14:20

But this does not yet compile. We need to beef up the Select table to have knowledge of the tables joined, as well as the join and where methods to keep track of that information.

WHERE 14:34

We can start by adding a generic to the Select type that represents the table joined: struct Select<From: Table, Join: Table>: QueryExpression { … }

WHERE 14:55

But we can go the extra mile here. It is possible to join many tables in a query, not just a single one. So we can encode the types of all of the joined tables into the Select type by again using parameter packs: struct Select<From: Table, each Join: Table>: QueryExpression { … }

WHERE 15:24

This allows for there to be any number of generics in Select , starting with From , and then with zero or more Join generics.

WHERE 15:27

And then we can update join so that it returns a Select with the Other generic specified: func join<Other: Table>( _ other: Other.Type, on constraint: (From.Columns, Other.Columns) -> some QueryExpression<Bool> ) -> Select<From, Other> { Select<From, Other>( columns: columns, joins: joins + [ "JOIN \(other.tableName) ON \(constraint(From.columns, other.columns).queryString)" ], orders: orders, wheres: wheres ) }

WHERE 15:53

With that change the library compiles, even though the Select type has been quite substantially changed, but tests are not yet compiling because of the where method. It too needs to be updated to pass the columns from the joined tables to the predicate. And this is where we can use even more of the power of parameter packs in Swift.

WHERE 16:24

Right now the predicate trailing closure of where looks like this: _ predicate: (From.Columns) -> some QueryExpression<Bool>

WHERE 16:30

It is only passing the columns of the From table to the closure. We also want to incorporate the columns of all the joined tables.

WHERE 16:38

Perhaps all we have to do is add the Join.Columns as an argument to the predicate: _ predicate: (From.Columns, Join.Columns) -> some QueryExpression<Bool> Pack reference ‘Join’ requires expansion using keyword ’repeat’ Type pack ‘Join’ must be referenced with ’each’ Well that causes a few compiler errors because Join is not a plain generic. It is a parameter pack.

WHERE 16:59

In order to destructure the parameter pack so that an argument for each generic can be provided to the closure, we must use repeat each has we have done a few times in this series: _ predicate: (From.Columns, repeat each Join.Columns) -> some QueryExpression<Bool> ‘each’ cannot be applied to non-pack type ’(each Join).Columns’

WHERE 17:12

This is close, but the each keyword can only be applied directly to parameter packs. And when Swift sees this: each Join.Columns

WHERE 17:17

…it thinks we are trying to destructure Join.Columns as a parameter pack. But Join.Columns is not a parameter pack, only Join is. So, we need to parenthesize like this: (each Join)

WHERE 17:31

…to tell Swift we want to destructure the Join parameter pack. Then we can further pluck out the Columns associated type from that parameter pack: (each Join).Columns And finally we repeat all of the columns of the joins as arguments of the predicate closure: _ predicate: (From.Columns, repeat (each Join).Columns) -> some QueryExpression<Bool>

WHERE 17:34

Next we need to update how we apply the predicate closure to the columns of our tables. We can use a very similar syntax as the closure signature to compute the columns for each type in the Join parameter pack and pass them to the predicate closure: wheres: wheres + [predicate(From.columns, repeat (each Join).columns).queryString]

WHERE 17:59

And just like that the library is compiling and even our tests are compiling. This means when chaining a where clause into a query after a join , we now get access to both tables. And if we run the test it does indeed generate the correct SQL query: @Test func joinWhere() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .where { !$1.isCompleted }, as: .sql ) { """ SELECT * FROM remindersLists JOIN reminders ON (remindersLists.id = reminders.remindersListID) WHERE NOT (reminders.isCompleted) """ } }

WHERE 19:11

This is absolutely incredible, but there are more methods that need the same treatment as where . We want to be able to order by any column in each joined table, as well as select columns from each joined table.

WHERE 19:19

Let’s write a test that shows how our current order method is not up to the task of ordering the results from joining two tables. Suppose we want to order the reminders and lists first by the title of the list, and then for all reminders in the same list we will further order them by the priority of the reminder, descending:

WHERE 19:42

We would hope we could write such a query like this: @Test func joinOrder() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .order { ($0.title, $1.priority.desc()) }, as: .sql ) }

WHERE 20:22

But this does not compile because our order method only deals with the columns of the From table: func order( @OrderBuilder build orders: (From.Columns) -> [String] ) -> Select { We need to incorporate the columns of the Join tables too in this trailing closure.

WHERE 20:37

We can do this in much the same way we did for where . We will add a new argument to the orders closure that expands each Join ’s Columns from the parameter pack: func order( @OrderBuilder build orders: (From.Columns, repeat (each Join).Columns) -> [String] ) -> Select {

WHERE 20:51

And then we need to update the construction of the Select to also pass along the columns from each Join generic: Select( columns: columns, joins: joins, orders: self.orders + orders(From.columns, repeat (each Join).columns), wheres: wheres )

WHERE 21:01

That is all it takes! We also have an overload of order that was implemented before we introduced the @OrderBuilder functionality. It’s not used at all, so let’s delete it.

WHERE 21:22

Now our tests are compiling, and the query generated by out newest test matches exactly what I would expect: @Test func joinOrder() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .order { ($0.title, $1.priority.desc()) }, as: .sql ) { """ SELECT * FROM remindersLists JOIN reminders ON (remindersLists.id = reminders.remindersListID) ORDER BY remindersLists.title, reminders.priority DESC """ } }

WHERE 21:54

We can even copy-and-paste the query into the SQL console to check that it works: sqlite> SELECT * ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = reminders.remindersListID) ...> ORDER BY remindersLists.title, reminders.priority DESC; ┌────┬──────────┬────┬─────────────────────┬─────────────┬──────────┬─────────────────┐ │ id │ title │ id │ title │ isCompleted │ priority │ remindersListID │ ├────┼──────────┼────┼─────────────────────┼─────────────┼──────────┼─────────────────┤ │ 3 │ Family │ 4 │ Buy concert tickets │ 1 │ 2 │ 3 │ │ 3 │ Family │ 1 │ Groceries │ 0 │ │ 3 │ │ 1 │ Personal │ 3 │ Take a walk │ 1 │ 1 │ 1 │ │ 1 │ Personal │ 2 │ Get haircut │ 0 │ │ 1 │ │ 1 │ Personal │ 6 │ Walk the dog │ 0 │ │ 1 │ │ 2 │ Work │ 5 │ Call accountant │ 0 │ 3 │ 2 │ └────┴──────────┴────┴─────────────────────┴─────────────┴──────────┴─────────────────┘

WHERE 22:17

And finally, we need to update our select method to allow for selecting any columns from any of the joined tables. Currently our select method is just a static function, which means it can only be used at the entry point of our query builder: RemindersList.select { $0.title }

WHERE 22:45

But we want to be able to join tables together and then select from the tables. To do that we need an instance method for select . As an example, suppose we want to select the titles of all reminders along with the title of their associated list. I would hope it could be done like this: @Test func joinSelect() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title) } as: .sql ) }

WHERE 23:37

This does not work right now because we don’t even have an instance method for select . But we do have a static select method, and the implementation of the instance method is going to be very similar. So, let’s start by copying and pasting the static method into the Select type and changing it to an instance method: func select<each ResultColumn: QueryExpression>( _ columns: (Columns) -> (repeat each ResultColumn) ) -> Select<Self> { let columns = columns(Self.columns) var columnStrings: [String] = [] for column in repeat each columns { columnStrings.append(column.queryString) } return Select( columns: columnStrings ) }

WHERE 24:25

The columns closure needs to be updated to take the columns of the From table, as well as each Join table: _ columns: (From.Columns, repeat (each Join).Columns) -> (repeat each ResultColumn)

WHERE 24:46

The return type of this method can just be Select with the generics inferred: ) -> Select {

WHERE 24:56

When invoking the columns closure we need to pass along From ’s columns as well as each Join ’s columns: let columns = columns(From.columns, repeat (each Join).columns)

WHERE 25:06

And then when we construct a new Select we will append these new columns to the existing columns, and pass along all other data: return Select( columns: self.columns + columnStrings, joins: joins, orders: orders, wheres: wheres )

WHERE 25:37

That is all it takes, and the whole library and test suite are now compiling. And if we run the test we wrote a moment ago we will see it generates exactly the query we expect: @Test func joinSelect() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title) }, as: .sql ) { """ SELECT remindersLists.title, reminders.title FROM remindersLists JOIN reminders ON (remindersLists.id = reminders.remindersListID) """ } }

WHERE 26:09

And we can combine all of these pieces to construct one larger, complex query. Say we wanted to select the titles of all incomplete reminders with their associated list and order the result set first by the list’s title, and then the reminder’s priority, descending. We can do so quite easily: @Test func joinSelectWhereOrder() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title) } .where { !$1.isCompleted } .order { ($0.title, $1.priority.desc()) }, as: .sql ) { """ SELECT remindersLists.title, reminders.title FROM remindersLists JOIN reminders ON (remindersLists.id = reminders.remindersListID) WHERE NOT (reminders.isCompleted) ORDER BY remindersLists.title, reminders.priority DESC """ } }

WHERE 27:27

And we can paste the large query into the SQL console to see that it does indeed work: sqlite> SELECT remindersLists.title, reminders.title ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = reminders.remindersListID) ...> WHERE NOT (reminders.isCompleted) ...> ORDER BY remindersLists.title, reminders.priority DESC; ┌──────────┬─────────────────┐ │ title │ title │ ├──────────┼─────────────────┤ │ Family │ Groceries │ │ Personal │ Get haircut │ │ Personal │ Walk the dog │ │ Work │ Call accountant │ └──────────┴─────────────────┘

WHERE 27:33

It’s really incredible to see how easy it was for us to get joins working in our query builder! Next time: Advanced joins

WHERE 27:38

We have now implemented a powerful syntax for representing joins in our query builder, and we are able to preserve the type information of each table involved so that we can write

WHERE 28:06

Let’s now push things even further. As we saw in last episode, the power of joins really starts to shine when it comes to aggregating data across joins. This is what allowed us to do things like selecting all lists from the database, along with a comma-separated list of the titles of reminders in each list. Another advanced query we previously considered was to select all lists from the database, along with a count of the reminders in each list.

WHERE 28:29

Let’s see what it takes to write such advanced queries with our builder…next time! References SQLite The SQLite home page https://www.sqlite.org Downloads Sample code 0321-sql-building-pt8 Point-Free A hub for advanced Swift programming. Brought to you by Brandon Williams and Stephen Celis . Content Become a member The Point-Free Way Beta previews Gifts Videos Collections Free clips Blog More About Us Community Slack Mastodon Twitter BlueSky GitHub Contact Us Privacy Policy © 2026 Point-Free, Inc. All rights are reserved for the videos and transcripts on this site. All other content is licensed under CC BY-NC-SA 4.0 , and the underlying source code to run this site is licensed under the MIT License .