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}%"))
