Ecto lower case on column

Questions / Help
, , , ,
1

Can I know how to write this query in elixir?

SELECT DISTINCT
	*
FROM
       "recipes"
	WHERE name ILIKE 'chicken%'
ORDER BY LOWER(name) ASC, created_on DESC;

I have two concerns

  1. Postgres returns the following error if lower function is used with DISTINCT
Query 1 ERROR: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 6: ORDER BY LOWER(name) ASC, created_on DESC;
  1. I would like to assign this line ORDER BY LOWER(name) ASC, created_on DESC; to a variable like sort_params in elixir and later use this variable in multiple places like ^sort_params. Looks like elixir has String.downcase which cannot be used like [desc: String.downcase(:name), desc: :created_on]. How to downcase in ecto? If we have to use fragment, can someone suggest an example that works for cases like my requirement here?

Thanks in advance :slight_smile:

2

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,
]
1 Like
3

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