Search code examples
elixirsql-likeecto

Ecto query where string field contains other string

I'm building a simple search feature and I want to find all the records that have a string within a string field.

Here's what I've attempted.

term = "Moby"
MyApp.Book
|> where([p], String.contains?(p, term))
|> order_by(desc: :inserted_at)

This would return the following books:

  • Moby Dick
  • Sandich Moby Lean
  • My Mobyand Example

But I get:

`String.contains?(p, term)` is not a valid query expression
Solution

  • You'll have to use String.replace/3 to escape the % in the input (if it's entered by the end user) and then use like inside the query:

    |> where([p], like(p.title, ^"%#{String.replace(term, "%", "\\%")}%"))

    Example:

    iex(1)> term = "Foo%Bar"
iex(2)> query = MyApp.Post |> where([p], like(p.title, ^"%#{String.replace(term, "%", "\\%")}%")) |> order_by(desc: :inserted_at)
#Ecto.Query<from p in MyApp.Post, where: like(p.title, ^"%Foo\\%Bar%"),
 order_by: [desc: p.inserted_at]>
iex(3)> Ecto.Adapters.SQL.to_sql(:all, MyApp.Repo, query)
{"SELECT p0.\"id\", p0.\"title\", p0.\"user_id\", p0.\"inserted_at\", p0.\"updated_at\" FROM \"posts\" AS p0 WHERE (p0.\"title\" LIKE $1) ORDER BY p0.\"inserted_at\" DESC",
 ["%Foo\\%Bar%"]}

    If you do not do the replace, a term like "a%b" would match "azb" as the % needs to be escaped or it matches any sequence of zero or more characters.