Video #335: Modern Search: Finesse

Episode: Video #335 Date: Aug 18, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep335-modern-search-finesse

Description

We now have a very basic search feature in place, but it can be improved. We will add some bells and whistles and other performance improvements, including debouncing database queries, adding a count query to display the number of completed reminders, and grouping the queries into a single transaction.

Video

Cloudflare Stream video ID: 5a88253d1a523104bca1908981e81725 Local file: video_335_modern-search-finesse.mp4 *(download with --video 335)*

References

Transcript

— 0:05

We now have rudimentary searching in our app. We simply split the user-entered search term on spaces to get an array of search sub-terms. And then we search for each of those sub-terms in the reminders table by checking if the term matches as a substring in the title or notes of the reminder. Brandon

— 0:21

Let’s amp things up a bit though. There are few fun bells and whistles that the real Reminders app in iOS implements in its search that we have not yet captured. For example, it shows the number of completed reminders that satisfy the search parameters and give you the option to delete all of those reminders or just hide them. You can also search tags, not just the title and notes of reminders.

— 0:46

Let’s see what it takes to add some of these more advanced querying options to our search feature. Rounding out the demo

— 0:53

Let’s start with a really simple improvement to the user experience of searching. Right now we are performing a search for every single key stroke in the search field. This means when searching for “Take walk” we will execute 9 SQL requests, and we really only care about the results from the last one. This is both inefficient and distracting for our user to see results flying in and out for search terms they are not interested in.

— 1:23

Luckily for us, modern Swift concurrency makes it incredibly easy for us to add debouncing logic to this feature. We can insert a small sleep directly in the unstructured task we spin up: searchTask = Task { try await Task.sleep(for: .seconds(0.3)) … }

— 1:42

This will make it so that if the user types a character, a task will spin up and suspend for 0.3 seconds, and if during that period of time the user types another character, then the task will be cancelled, causing Task.sleep to throw a cancellation error, and thus preventing us from loading a new query into the $rows projected value.

— 2:03

But, our searchTask currently has Never as its failure type, and so we now need to update that to be any Error : var searchTask: Task<Void, any Error>?

— 2:13

That’s all it takes and we can run the app in the simulator to see that indeed the search queries are debounced.

— 2:27

And we could even take control of this debounce using a clock dependency, but we have done that many times on Point-Free, and so we’ll leave that as an exercise for the viewer.

— 2:53

Next let’s move onto another simple thing missing from our search feature, which is that currently when we render the ReminderRow in search results we are hard-coding a bunch of its data: ReminderRow( color: .blue, isPastDue: false, reminder: reminder, tags: [], onDetailsTapped: {} )

— 3:03

We are hard-coding the color to blue, the past due state to false and an empty array of tags. Let’s see what it takes to alter our query to grab all of this data while searching.

— 3:13

We will start by modeling a data type that holds all the data we need to provide this row, and it will be annotated with the @Selection macro so that it can be decoded from data pulled from SQLite: @Selection struct Row { let color: Int let isPastDue: Bool let reminder: Reminder @Column(as: [String].JSONRepresentation.self) let tags: [String] }

— 4:03

And because SQLite has no native type to represent arrays, and because we do want to pull the array of tag titles from the database, we annotate the tags field with the @Column(as:) macro so that it knows how to encode and decode the data to shuffle it from SQLite to Swift.

— 5:07

With that done we can update the state that our model holds onto: @ObservationIgnored @FetchAll var rows: [Row]

— 5:36

Now we just have to adapt our search query to select everything needed from our tables and package it up into this data type. To guide us, we can start by performing a select on our query, which currently has access to the reminders table definition, and then inside that trailing closure we want to construct Row.Columns : .select { reminder in Row.Columns( color: <#QueryExpression#>, isPastDue: <#QueryExpression#>, reminder: <#QueryExpression#>, tags: <#QueryExpression#> ) }

— 6:12

Now some of this data is easy to provide already, such as the isPastDue state and reminder : .select { reminder in Row.Columns( color: <#T##QueryExpression#>, isPastDue: reminder.isPastDue, reminder: reminder, tags: <#T##QueryExpression#> ) }

— 6:18

Because remember, isPastDue is not an actual column on our table, but rather a helper that we defined in past episodes that does the date math for us: extension Reminder.TableColumns { var isPastDue: some QueryExpression<Bool> { !isCompleted && (dueDate ?? Date.distantFuture) < Date() } }

— 6:33

Next we need the color associated with this reminder, which is really just the color of the reminders list that this reminder belongs to. So, sounds like we need to join the reminders list table into this query so that we can pluck out its color, and since every reminder has a list, we can just use a regular join instead of a left join: .join(RemindersList.all) { $0.remindersListID.eq($1.id) } .select { reminder, remindersList in Row.Columns( color: remindersList.color, isPastDue: reminder.isPastDue, reminder: reminder, tags: <#QueryExpression#> ) }

— 7:43

Next we need to query for all the tags associated with each reminder. We’ve done this kind of thing in the previous series of episodes, but we will do it again from scratch here because it’s a pretty fun technique.

— 8:01

First, to get access to tags we need to join the tags table, and not every reminder has a tag so it has to be a left join: .leftJoin(Tag.all) { }

— 8:23

But, the Tag table does not have a direct foreign key pointing to a Reminder . Instead, there is a join table called ReminderTag that allows us to associate any number of tags to any number of reminders, and vice-versa.

— 9:10

So, we must join that first by matching the reminder IDs: .leftJoin(ReminderTag.all) { $0.id.eq($2.reminderID) }

— 9:43

And then we can join the Tag table by matching the tag IDs: .leftJoin(Tag.all) { $2.tagID.eq($3.id) }

— 10:10

And now we have access to all of these tables in our select : .select { reminder, remindersList, _, tag in … }

— 10:37

To bundle up all of the tags into an array of strings we will encode their titles into a JSON array, which allows us to shuffle the data over to Swift, at which point Swift will decode that data into a Swift array thanks to the JSONRepresentation we used in Row :

— 11:02

In the last series of episodes we should how to do this using the jsonGroupArray method: tags: (tag.title ?? "").jsonGroupArray(filter: tag.id.isNot(nil))

— 11:45

But we also encapsulated that bit of logic into its own helper called jsonTitles defined on the tag’s table definition: Row.Columns( color: remindersList.color, isPastDue: reminder.isPastDue, reminder: reminder, tags: tag.jsonTitles )

— 12:40

We can now make use of this updated query in the view: ForEach(model.rows, id: \.reminder.id) { row in ReminderRow( color: Color(hex: row.color), isPastDue: row.isPastDue, reminder: row.reminder, tags: row.tags, onDetailsTapped: {} ) }

— 14:18

If we run the preview and search “take” we will see a single reminder “Take a walk” but we know there is also a “Take out trash” reminder. The problem is we are aggregating many rows of the joined tables into a single reminder row, and so we need to make sure to group by the reminder’s ID: Reminder .group(by: \.id) …

— 15:10

We can now run the app to see that everything works. We are correctly showing the due date of a reminder as red when it’s past due. More bells and whistles

— 15:25

We’ve now got the reminder rows in search rendering the exact way they do in the detail view. And the best part is that we were able to reuse many of the helpers we created for the detail view over in this new view. This shows that when there is a lot of commonality in how data is fetched for two different views, one of the best places to extract helpers out to is the SQL querying layer. That will give you the best chance of being able to share that logic with as many places as possible. Stephen

— 15:53

Let’s keep adding little bells and whistles to our reminders app. We are now in a position to layer on some more advanced functionality in search. For one thing, now that tags are joined into this query, we have access to them to further search for reminders whose tag is one of the search terms entered by the user. And we can implement the functionality in Apple’s official reminders app that counts the number of completed reminders matching your search term.

— 16:15

Let’s dig in.

— 16:18

With this work done we are now in a position to layer on some more advanced functionality in search. For one thing, now that tags are joined into this query, we have access to them to further search for reminders whose tag is one of the search terms entered by the user. To do this we need access to tags in the where clause, which means we need to move it after all of the joins: Reminder .group(by: \.id) .join(RemindersList.all) { $0.remindersListID.eq($1.id) } .leftJoin(ReminderTag.all) { $0.id.eq($2.reminderID) } .leftJoin(Tag.all) { $2.tagID.eq($3.id) } .where { reminder, _, _, tag in … }

— 16:38

And now we would love if we could just add another || to our where clause to further check if the search term is contained in the tag’s title: .where { reminder, _, _, tag in for term in searchText.split(separator: " ") { reminder.title.contains(term) || reminder.notes.contains(term) || tag.title.contains(term) } }

— 16:49

Or, maybe even better, we could be a little more strict by requiring that the search term is the prefix of the tag’s title: .where { reminder, _, _, tag in for term in searchText.split(separator: " ") { reminder.title.contains(term) || reminder.notes.contains(term) || tag.title.hasPrefix(term) } }

— 17:10

However, this does not compile. The contains and hasPrefix methods are defined on query expressions of strings, and secretly tag.title is actually a query expression of an optional string. This is happening because we had to left join the tags table in our query since not every reminder necessarily has a tag. This means we have to explicitly deal with the optionality of the tag table, and we can do this by just coalescing the value to an empty string: || (tag.title ?? "").hasPrefix("\(term)")

— 17:39

Now when we run the app and search for “week” we will see that it has found “Groceries”, “Haircut” and “Take a walk” because they are all assigned the “#weekend” tag, and it has further found the reminder “Send weekly emails” because the title contains the string “week”. That’s pretty amazing!

— 17:57

The next thing we are going to recreate is the header that is placed above the search results that lets you know how many completed reminders are in the search results, as well as the ability to show or hide the completed items. This will give us an opportunity to show how to break down a large query into smaller pieces that can be shared, and how to group multiple related queries into a single transaction.

— 18:14

Let’s start by adding some new state to our model that represents the number of completed reminders for the current search query: @ObservationIgnored @FetchOne var completedCount = 0

— 18:29

Then when it is time to update the query we will need to load a new counting query into the projected value of this state: $completedCount.load(…)

— 18:40

And the work we need to do in this query is very similar to what we did for searching these reminders, except this time we will compute the count of completed reminders instead of fetching the actual reminder data.

— 18:49

Luckily for us our query builder is very flexible, and it is possible for us to define an intermediate query that represents the core of what we want to fetch from the database, and then we can further customize it to either fetch just the count of completed reminders, or fetch the reminders themselves.

— 19:02

We can start by defining a local query value that consists of the entire query except for the select clause: let query = Reminder .group(by: \.id) .join(RemindersList.all) { $0.remindersListID.eq($1.id) } .leftJoin(ReminderTag.all) { $0.id.eq($2.reminderID) } .leftJoin(Tag.all) { $2.tagID.eq($3.id) } .where { reminder, _, _, tag in for term in searchText.split(separator: " ") { reminder.title.contains(term) || reminder.notes.contains(term) || tag.title.hasPrefix(term) } }

— 19:19

This query performs all the joins necessary to allow it to search reminder titles, notes and tags for the search terms. However, it also has the group clause in it, which is only necessary when aggregating the tags of a reminder together. We don’t want that when computing the count of reminders because that will group the counts on a per-reminder basis, and that isn’t the computation we are looking for. We are looking for the full count of all reminders.

— 19:39

So, let’s drop the group clause from our base query: let query = Reminder .join(RemindersList.all) { $0.remindersListID.eq($1.id) } .leftJoin(ReminderTag.all) { $0.id.eq($2.reminderID) } .leftJoin(Tag.all) { $2.tagID.eq($3.id) } .where { reminder, _, _, tag in for term in searchText.split(separator: " ") { reminder.title.contains(term) || reminder.notes.contains(term) || tag.title.hasPrefix(term) } }

— 19:43

Now we can refactor loading the query into $rows by using this base query and adding the few extra bits it needs to do its job: try await $rows.load( query .group { reminder, _, _, _ in reminder.id } .select { reminder, remindersList, _, tag in Row.Columns( color: remindersList.color, isPastDue: reminder.isPastDue, reminder: reminder, tags: tag.jsonTitles ) } }

— 19:52

And now we are in a position to load a query into $completedCount . We will start with the base query: try await $completedCount.load( query )

— 19:58

And from this base query we want to select only the count of reminders in the result set. We can do this with a select clause that counts the reminders’ IDs: try await $completedCount.load( query .select { reminder, _, _, _ in reminder.id.count() } )

— 20:12

However, there is the potential to have duplicates of certain reminders in this result set because we are joining the tags table, and a single reminder can have multiple tags. So we further want to compute this count with a distinct option: try await $completedCount.load( query .select { reminder, _, _, _ in reminder.id.count(distinct: true) } )

— 20:25

This will currently count all reminders satisfying the search term, but we further want to count only the completed ones. This is easy enough to do by adding another where clause: try await $completedCount.load( query .where { reminder, _, _, _ in reminder.isCompleted } .select { reminder, _, _, _ in reminder.id.count(distinct: true) } )

— 20:39

We can further simplify things. The base query is still joining the reminders lists table, but the completed count query isn’t using this information at all, so let’s move the join to the rows query: let query = Reminder .leftJoin(ReminderTag.all) { $0.id.eq($1.reminderID) } .leftJoin(Tag.all) { $1.tagID.eq($2.id) } .where { reminder, _, tag in for term in searchText.split(separator: " ") { reminder.title.contains(term) || reminder.notes.contains(term) || tag.title.hasPrefix(term) } } try await $rows.load( query .group { reminder, _, _ in reminder.id } .join(RemindersList.all) { $0.remindersListID.eq($3.id) .select { reminder, remindersList, _, tag in Row.Columns( color: remindersList.color, isPastDue: reminder.isPastDue, reminder: reminder, tags: tag.jsonTitles ) } } try await $completedCount.load( query .where { reminder, _, _ in reminder.isCompleted } .select { reminder, _, _ in reminder.id.count(distinct: true) } )

— 21:26

And that is all it takes. We aren’t yet displaying this data in the view, so let’s head down to the body of the view and add a HStack header to the list: var body: some View { HStack { Text("\(model.completedCount) Completed") Spacer() Button("Show completed") { } } … }

— 21:50

Now when we run the app in the simulator we will see the number of completed reminders dynamically update as we search for various terms. If we type “Take” we see there is 1 completed reminder. If we type “Week” we will see there are 2 completed reminders. And if we check off “Groceries” we will see that the count immediately updated to 3.

— 22:05

We can also take a look at the logs to see the massive query that is being generated by just a few lines of code using the SQL builder: 0.001s SELECT count(DISTINCT "reminders"."id") FROM "reminders" JOIN "remindersLists" ON ("reminders"."remindersListID" = "remindersLists"."id") LEFT JOIN "reminderTags" ON ("reminders"."id" = "reminderTags"."reminderID") LEFT JOIN "tags" ON ("reminderTags"."tagID" = "tags"."id") WHERE ((("reminders"."title" LIKE '%Week%') OR ("reminders"."notes" LIKE '%Week%')) OR (coalesce("tags"."title", '') LIKE 'Week%')) AND ("reminders"."status" <> 0) 0.000s SELECT "remindersLists"."color" AS "color", (NOT (("reminders"."status" <> 0)) AND (coalesce("reminders"."dueDate", '4001-01-01 00:00:00.000') < '2025-08-06 16:17:56.330')) AS "isPastDue", "reminders"."id", "reminders"."createdAt", "reminders"."dueDate", "reminders"."isFlagged", "reminders"."notes", "reminders"."priority", "reminders"."remindersListID", "reminders"."status", "reminders"."title", "reminders"."updatedAt" AS "reminder", json_group_array(coalesce("tags"."title", '')) FILTER (WHERE ("tags"."id" IS NOT NULL)) AS "tags" FROM "reminders" JOIN "remindersLists" ON ("reminders"."remindersListID" = "remindersLists"."id") LEFT JOIN "reminderTags" ON ("reminders"."id" = "reminderTags"."reminderID") LEFT JOIN "tags" ON ("reminderTags"."tagID" = "tags"."id") WHERE ((("reminders"."title" LIKE '%Week%') OR ("reminders"."notes" LIKE '%Week%')) OR (coalesce("tags"."title", '') LIKE 'Week%')) GROUP BY "reminders"."id"

— 22:16

I also noticed that the completed reminder did not sort down to the bottom when I checked it off, and in fact we are not sorting the search results at all. One quick improvement we can make is to sort the results so that the completed reminders are always at the bottom: .order { reminder, _, _, _ in reminder.isCompleted }

— 22:36

Now we can see that the reminders re-sort after completing one, but it did so without animation. To add animation we just have to specify what animation we want to use when loading the new query: try await $rows.load( …, animation: .default )

— 22:54

And now everything animates beautifully.

— 23:00

We are going to make one last improvement to this search feature, which is to group the two queries for computing a count and getting the search results into a single transaction. Right now the data for these queries is held in two separate properties, which means they constitute two separate subscriptions to the database and technically they can deliver their results at different times. This means if the database changes or the search term changes, there could theoretically be a brief window of time where one query finishes before the other causing data to be displayed in the UI in an inconsistent state.

— 23:36

It’s typically not a problem in practice, but if you have multiple queries powering a view and they typically update at the same time, as is the case right now, it can be a good practice to group them into a single transaction. To do this one defines a type that conforms to FetchKeyRequest : struct SearchRequest: FetchKeyRequest { }

— 24:02

Inside this type we will be able to make any number of database requests to get the data needed for the view, and all of those requests will be performed within a single transaction.

— 24:09

To implement this conformance we need to define a Value type to satisfy the associated type requirement of the protocol, and this type represents all of the data we want to fetch from the database. In this case, it’s the completed count and array of rows: struct SearchRequest: FetchKeyRequest { struct Value { var completedCount = 0 var rows: [Row] = [] } }

— 24:26

Next we need to implement a fetch method to satisfy that requirement from the protocol, and this method is handed an opaque database handle that can be used to execute queries: struct SearchRequest: FetchKeyRequest { struct Value { var completedCount = 0 var rows: [Row] = [] } func fetch(_ db: GRDB.Database) throws -> Value { } }

— 24:32

Inside this method we will need to return a Value : struct SearchRequest: FetchKeyRequest { struct Value { var completedCount = 0 var rows: [Row] = [] } func fetch(_ db: GRDB.Database) throws -> Value { Value( completedCount: <#Int#>, rows: <#[Row]#> ) } }

— 24:38

And for this we can basically copy-and-paste the queries we wrote when loading the $completedCount and $rows property wrappers. But, those queries depend on having access to the searchText , and so we will need to add that as a property to the request struct: struct SearchRequest: FetchKeyRequest { let searchText: String … } And now we can basically copy-and-paste our previous queries and make just a few small changes: func fetch(_ db: GRDB.Database) throws -> Value { let query = Reminder .join(RemindersList.all) { $0.remindersListID.eq($1.id) } .leftJoin(ReminderTag.all) { $0.id.eq($2.reminderID) } .leftJoin(Tag.all) { $2.tagID.eq($3.id) } .where { reminder, _, _, tag in for term in searchText.split(separator: " ") { reminder.title.contains(term) || reminder.notes.contains(term) || tag.title.hasPrefix(term) } } return Value( completedCount: try query .where { reminder, _, _, _ in reminder.isCompleted } .select { reminder, _, _, _ in reminder.id.count(distinct: true) } .fetchOne(db) ?? 0, rows: try query .group { reminder, _, _, _ in reminder.id } .order { reminder, _, _, _ in reminder.isCompleted } .select { reminder, remindersList, _, tag in Row.Columns( color: remindersList.color, isPastDue: reminder.isPastDue, reminder: reminder, tags: tag.jsonTitles ) } .fetchAll(db) ) }

— 25:33

Now we just need to hook up this request. We can replace the two distinct property wrappers with a single @Fetch : // @ObservationIgnored // @FetchAll var rows: [Row] // // @ObservationIgnored // @FetchOne var completedCount = 0 @ObservationIgnored @Fetch var searchResults = SearchRequest.Value()

— 25:58

And load it when the query changes: try await $searchResults.load( SearchRequest(searchText: searchText), animation: .default )

— 26:15

And update the view to access things through the search results.

— 26:27

Now the app should work exactly the same, but we now guarantee that the completedCount state and the rows state both change at exactly the same time, and all of the database work is done in a single transaction.

— 26:39

We can even take a look at the logs to see that indeed both queries are put into a single transaction: 0.000s BEGIN DEFERRED TRANSACTION 0.000s SELECT count(DISTINCT "reminders"."id") FROM "reminders" JOIN "remindersLists" ON ("reminders"."remindersListID" = "remindersLists"."id") LEFT JOIN "reminderTags" ON ("reminders"."id" = "reminderTags"."reminderID") LEFT JOIN "tags" ON ("reminderTags"."tagID" = "tags"."id") WHERE ((("reminders"."title" LIKE '%Week%') OR ("reminders"."notes" LIKE '%Week%')) OR (coalesce("tags"."title", '') LIKE 'Week%')) AND ("reminders"."status" <> 0) 0.000s SELECT "remindersLists"."color" AS "color", (NOT (("reminders"."status" <> 0)) AND (coalesce("reminders"."dueDate", '4001-01-01 00:00:00.000') < '2025-08-06 16:26:22.024')) AS "isPastDue", "reminders"."id", "reminders"."createdAt", "reminders"."dueDate", "reminders"."isFlagged", "reminders"."notes", "reminders"."priority", "reminders"."remindersListID", "reminders"."status", "reminders"."title", "reminders"."updatedAt" AS "reminder", json_group_array(coalesce("tags"."title", '')) FILTER (WHERE ("tags"."id" IS NOT NULL)) AS "tags" FROM "reminders" JOIN "remindersLists" ON ("reminders"."remindersListID" = "remindersLists"."id") LEFT JOIN "reminderTags" ON ("reminders"."id" = "reminderTags"."reminderID") LEFT JOIN "tags" ON ("reminderTags"."tagID" = "tags"."id") WHERE ((("reminders"."title" LIKE '%Week%') OR ("reminders"."notes" LIKE '%Week%')) OR (coalesce("tags"."title", '') LIKE 'Week%')) GROUP BY "reminders"."id" ORDER BY ("reminders"."status" <> 0) 0.000s COMMIT TRANSACTION Next time: Full-text search

— 27:01

OK, we have now explored some pretty advanced topics in searching for text inside of a database. The actual search technique is pretty rudimentary, just splitting on the search text to get each search term, and then checking if various columns contain those search terms. But we have layered on some real world, advanced requirements too, such as searching the reminders’ titles, notes and tags, and we are computing a count of completed reminders right alongside the search results. Brandon

— 27:25

But let’s now improve on the actual search technique being employed here. There is a technology known as “full-text search” that is built into many databases, such as Postgres and MySQL, and of course SQLite has fantastic support for it. Full-text search allows you to maintain a separate table, or really tables , of indexed information about the text in your application so that it can quickly and efficiently search large corpuses of data. It also has some fancy tricks up its sleeve that allow you to rank search results and even highlight the search terms in the results.

— 28:03

Let’s see what it takes to replace our rudimentary substring search technique with a more high-powered full-text search technique…next time! References SQLiteData Brandon Williams & Stephen Celis A fast, lightweight replacement for SwiftData, powered by SQL. https://github.com/pointfreeco/sqlite-data StructuredQueries A library for building SQL in a safe, expressive, and composable manner. https://github.com/pointfreeco/swift-structured-queries Downloads Sample code 0335-fts-pt2 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 .