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 use
fragmentto inject custom SQL into our query.
email = "email@example.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) ["firstname.lastname@example.org"]
Fragment has generated this
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