Arel, part I: Case-insensitive searches with partial matching - rails

Here at SciMed, we find ourselves using several different databases in our day-to-day work. While we always ensure that we're cognizant of the differences between database options—not being able to pass more than one thousand options to an IN query with Oracle, for example—the code that we write tends to be pretty database-agnostic across different applications.

These similarities are due to several reasons. First, we tend to stick to relational SQL databases for our projects. While there are definitely some big differences between, say, Postgres and MySQL, simple queries tend to be very similar—if not equivalent—between the two flavors of SQL. We also do our best to keep our code DRY whenever it's appropriate, leading us to create several internal tools as well as open-source gems like Topographer to allow for easy code reusability.

Another reason that our data-accessing code tends to look similar regardless of our database is due to our ORM of choice, ActiveRecord. As one of the core components of Rails, ActiveRecord provides a pretty straightforward interface between a Rails application's Ruby models and the underlying tables in its database. It contains adapters for a variety of different database options, such as SQLite, MySQL, or Postgres, which allow you to write the same code to query different types of underlying databases through ActiveRecord::QueryMethods.

Unfortunately, ActiveRecord::QueryMethods can quickly feel insufficient once your queries get a bit on the complicated side. For example, when you want to start writing OR statements, multiple self-joins, or fuzzy searches, ActiveRecord alone will start to let you down.

Luckily for those of us who write applications that can rarely—if ever—be classified as "simple", Rails allows you to access Arel, the SQL abstract syntax tree (AST) which ActiveRecord is built upon. While using it in conjunction with ActiveRecord requires writing more verbose code than your everyday ActiveRecord code—a simple Post.where(name: 'Awesome post') in ActiveRecord becomes Post.where(Post.arel_table[:name].eq('Awesome post')) with Arel—it gives you more options and power when writing your queries without forcing you to resort to writing raw SQL. This can be a huge plus if there's any possibility that your project may switch to a different type of ActiveRecord-supported database in the future.

Arel's README covers a lot of basic queries, from those that ActiveRecord can provide—like basic SELECT statements—to those that you can't, such as queries that utilize comparison operators like > or <. In the coming weeks, we'll be publishing several blog posts on Arel recipes that we at SciMed have found quite useful. If you've never used Arel before, you'll probably want to have gone through the Arel README at least once before reading these tidbits to maximize your understanding. Further, note that these queries are meant to provide simple examples of lesser-known Arel techniques—there may be better or easier solutions for the situations we detail.

With our introduction out of the way, let's get started with our first Arel recipe!

In addition to efficient code and clean design, one of our priorities at SciMed is great user experience (UX). In many cases, this is partially accomplished by building out functionality that gives our users a good deal of flexibility. One bit of functionality that we commonly provide to our clients is case-insensitive search pages that look for partial—rather than exact—matches.

For anyone that's only used ActiveRecord by itself, the where method may immediately come to mind as a possible solution. That line of thinking will likely lead to a rough draft of the following form if we're attempting to search on an Article model:

Article.where(title: target_title)

Depending on your database configuration, this may only do case-sensitive, exact matches. If your user just remembers one of the words in the article title they're looking for—for example, Rails—they might not get back Advanced Rails Topics or even RAILS. Luckily, Arel gives us all we need to ensure our intended behavior with the following query:

Article.where(
  Article.arel_table[:title]
    .lower
    .matches("%#{target_title.downcase}%")
)

If the user passes in Rails in the title search field, this will generate the following SQL:

SELECT "ARTICLES".*
FROM "ARTICLES"
WHERE (LOWER("ARTICLES"."TITLE") LIKE '%rails%')

This will correctly give you back any results that contain the string Rails—or any case variant—within their title. Do make sure that, if the column you're searching on can contain the % character, your test suite checks to make sure the behavior is as you intend.