My Rails app has a model called
Account, and I’ve been tasked with implementing an account search feature. Users type their search query into a form and see a list of accounts which match that query. The problem is that exactly what constitutes a ‘match’ is complicated. Each
Account has multiple attributes (
first_name etc.) which could match the search query, and even some associated models (e.g. an account
has_many :phone_numbers) whose attributes should be searchable too.
The standard ActiveRecord API isn’t going to cut it here. Maybe I should have just built this app using Sequel, but for now I’m stuck with ActiveRecord, so it looks like I’m going to have to get my hands dirty and write some raw SQL.
For the purposes of this article, I’m not going to worry about the front-end interface of my search feature. All I care about for now is creating the function that actually does the searching. Here’s the shell of a class called
Account::Search which I need to finish:
I’m following the Trailblazer convention of encapsulating this logic within a single-purpose class that responds to
call method takes a string (the search query) as an argument, and it will return a collection of Accounts.
What should go in the body of the
call method? I have no idea. The requirements are too complicated, so to get started I’m going to implement a simplified version of the feature and see where I can go from there.
Let’s start by making Accounts searchable by just one attribute - their
accounts table. Search should be case-insensitive, and partial matches should return results (so for example if the email address is
firstname.lastname@example.org, you should be able to find it if you just search for
This can be easily accomplished using the the PostgreSQL
Simple enough. Note that we have to add a
% to the beginning and end of the query string to allow partial matches - you can think of
% in PostgreSQL as being roughly equivalent to
.* in a normal Ruby regex. Also note that the
ILIKE keyword is case-insensitive - the case-sensitive equivalent is called
That was too easy. An Account also has the attributes
first_name (non-nullable) and
last_name (nullable), and these should be searchable too. How can we make our search function return accounts where the query matches any of these three attributes, not just
Normally I’d follow TDD and create some RSpec tests for my as-yet-unfinished
Account::Search class, but for the sake of brevity I’ll just provide some example data here that you can create in the rails console (remember that
last_name can be
Here’s my first attempt:
This appears to be working… but what if we want to search for someone by their full name? Right now I can find Paul Robertson by searching for “Paul” or for “Robertson”, but if I search for the full string “Paul Robertson” I get no results. That won’t do. Back to the drawing board:
|| is the PostgreSQL operator for concatenation, and doesn’t mean ‘or’ like it does in Ruby. So what I’m doing here is, for each account, concatenating its email, first name, and last name into a single string (with some whitespace in between, so e.g. for Rob we get
'email@example.com Robert Brown') and comparing the search query (case-insensitiv (case-insensitively)ely) against that full string. Now we can find Paul Robertson if we search for him by his full name.
Not so fast - something’s still not right. Look what happens when we search for
"rob" with our new method:
These search results should also include Bob, since his email address is
firstname.lastname@example.org, but he’s not showing up. What’s going on? I notice that Bob is the only account in my test data that doesn’t have a last name; maybe this is what’s causing the problem.
To investigate, let’s forget about Ruby for a second and look directly into the database via the PostgreSQL console, which you can fire up using
rails dbconsole or just
psql name_of_database. Let’s see the ‘searchable’ string that’s created for each one of our accounts:
Yerwhat? Why is the ‘searchable’ column blank for
It turns out that this is a quirk of concatenation in SQL: anything concatenated with
NULL. I sup suppose it kinda makes sense when you think about it: what does it even mean to concatenate a string with the null value? Trying to do it in Ruby (
'string' << nil) will raise an error. So since Bob’s last_name is
NULL, his entire searchable string evaluates to
NULL as well, and will match no queries.
Thankfully, with a bit more digging I discovered Postgres’s
concat operator, which treats
NULLs like empty strings:
Awww yeah! In fact, this can be made a bit prettier using a slightly different function called
concat_ws(' ', email, first_name, last_name)
You can think of this as being equivalent to
[email, first_name, last_name].join(' ') in Ruby, noting that if
join will treat it like an empty string.
At last, I have a serviceable first approximation of my search function. I’ll just make one more little improvement: before I pass the query string to ActiveRecord, I’ll call
squish on it, so that for example searching for
Mike Smith will have the same results as searching for
Mike Smith. Et voila:
There’s still much more room for improvement. What if we search for someone with their names in the wrong order, like ‘Smith Mike’? That seems like a case we should handle. We also need to test how long this search will take against a large database and if there are any ways we can speed it up - we’ll almost certainly want to add some indexes to our
accounts table to make the search run faster. And of course, in reality my
Account model is much more complicated than this - there are more columns which have their own considerations, and there are some other models associated with
Account which should also be considered in our search results.
I’ll write about of all that in a future article. Until then, happy searching!
Thanks to this StackOverflow question and its answers for getting me on the right track when I was figuring all of this out.
The code examples in this article use:
Want to make an improvement or correction to this article? Submit a PR on Github