Ecto lower case on column

For complex ordering you’ll need a fragment

There’s an example in the docs showing how a fragment can be used with order_by:

from c in City, order_by: [
  # A deterministic shuffled order
  fragment("? % ? DESC", c.id, ^modulus),
  desc: c.id,
]

Thanks for your response.

I tried to use fragment for the following query but I think I am not able to replicate the same query on Elixir. My syntax is probably wrong

where (lower(name) = lower('matching_term') or lower(body) = lower('matching_term'))or (name ilike '%matching_term%' or body ilike '%matching_term%') 
order by
CASE
when lower(name) = lower('matching_term') or lower(body) = lower('matching_term') then 0
else 1

Rather than passing the then/else output into order by, I would like to store it in a variable and reuse it in different part of the code. Like I would like to store the then/else output in a variable called sort_bys and pass it to order_by like order_by(^sort_bys) and pagination.

Elixir ecto

matching_term = String.downcase(matching_term)
    from r in Recipes,
    where:
    fragment("CASE WHEN ? THEN ? ELSE ? END",
    ("lower(?)", r.name) == ^"#{matching_term}" or ("lower(?)", r.body) == ^matching_term,
    fragment(("lower(?)", r.name == ^matching_term) or ("lower(?)", r.body == ^matching_term)),
    (r.name ilike ^"%#{matching_term}%" or r.body ilike ^"%#{matching_term}%"))