OK, we now have the basics of full-text search in place for our reminders app. When reminders are created, updated or deleted, we update the full-text search index with the newest information, which gives us immediate and efficient access to searching through our reminders. And in the process we were able to simplify our queries, reduce the number of tables we have to join, and just generally offload the complexities of search to the FTS5 module in SQLite.

But we haven’t captured all of the behavior we had previously when searching with the simple SQL LIKE operator and using patterns. In particular, we currently cannot search for tags that are assigned to reminders. This is a little more complicated because tags are held in a table separate from reminders, and they even form a many-to-many association to reminders, which means there’s a 3rd join table to contend with too.

But, luckily for us it is quite easy to deal with using just a few more triggers. So, let’s take a look.