Ecto search keyword interpolation

Updated on 2023-11-27:

Now I realize I didn't need to use a fragment. I could have used ilike.

The difference between ilike and like is that ilike is explicitly case-insensitive, whereas like could be case sensitive based on the database you are using.

Original post:

I was doing Dockyard's tutorial of Elixir and Phoenix. It took me an hour to figure out how to search a field for partial matching strings.

tl;dr, here's the code:

def search_posts(keyword) do
  search_pattern = "%#{keyword}%" # ❤️
  Repo.all(
    from p in Post,
    where:
      fragment("? LIKE ?",
          p.title,
          ^search_pattern)
  )
end

Inside an Ecto.Query statement, we need to use the caret to interpolate a variable. And this was novel to me. (Macros in Elixir enable innovative syntax, which is certainly powerful, but tends to be elusive to beginners.)

If we wrote SQL, it would be something like this, % being the wildcard for any number of chars.

SELECT * WHERE 'title' LIKE '%word%'

In order to send a query that looks something like the above, I used a fragment:

def search_posts(keyword) do
  Repo.all(
    from p in Post,
    where: fragment("? LIKE %?%", p.title, ^keyword)
  )
end

For some reason the SQL query after interpolation ended up looking like this.

SELECT p0."id", p0."title", p0."subtitle",
p0."content", p0."inserted_at",
p0."updated_at"
FROM "posts" AS p0
WHERE (p0."title" LIKE %$1%)

That's why I defensively used interpolation to get search_pattern on line ❤️ .