Ecto queries using "fragment" in Elixir

Ecto’s query syntax does not cater to all types of database queries. fragment is useful to inject custom SQL for composing queries.

Lets take a very simple example to lowercase a target column while comparing a string in where clause in our SQL query. We can usefragmentto inject custom SQL into our query.

email = "test@hello.com"

query = from u in User, 
  where: fragment("lower(?)", u.email) == ^email, 
  select: u.email

Repo.all query

In the example above, we have used lower using fragment to lowercase (i.e. downcase) the email column. This will generate following query

[debug] QUERY OK source="users" db=3.9ms queue=0.1ms
SELECT u0."email" FROM "users" AS u0 WHERE (lower(u0."email") = $1) ["test@hello.com"]

Fragment has generated this WHERE (lower(u0."email")

Note: fragment expects the first argument to be a string for SQL fragments. Also, when fragments are used, Ecto does not do type casting so we have to explicitly take care of it.

Now lets say we have email value as " Test@Hello.Com " i.e. with few capital letters and spaces. Lets lowercase and trim spaces in email string for our comparison in query.

email = 
  email
  |> String.downcase()
  |> String.trim()

As per Elixir style guide, its highly recommended to use parentheses for one-arity functions when using the pipe operator. Here is the link to Elixir style guide which recommends preferred way of writing it.

If we had to just trim space and not lowercase string then its recommended to avoid using pipe operator for just one call. We can simply write it as String.trim(email). Even Elixir style guide here recommends doing this way without using pipe operator.

We can use fragments to inject much more complicated custom SQL to compose a query.

Reference: Ecto query API - fragment function