Using dynamic with order_by

Hello,
I’d like to use dynamic/2 and order_by the following way:

    order_by =
      case params["order_by"] do
        "price_asc" -> dynamic([i, p], asc: i.price)
        "price_desc" -> dynamic([i, p], desc: i.price)
        _ -> dynamic([i, p], desc: i.released_at)
      end

but it generate the following issue:

** (Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size
    expanding macro: Ecto.Query.dynamic/2

I used successfully dynamic in other part of the code, and I can’t figure out why it is an issue there

Regards

Did you import Ecto.Query?

dynamic is used for building where clause fragments. The macro you need to use is order_by. You will need to pass it the queryable, e.g.:

query = from i in IThing,
            join: p in assoc(i, :pthing)

query =
      case params["order_by"] do
        "price_asc" -> order_by(query, [i, p], asc: i.price)
        "price_desc" -> order_by(query, [i, p], desc: i.price)
        _ -> order_by(query, [i, p], desc: i.released_at)
      end
2 Likes

@LostKobrakai yes
@jeremyjh thank you! It works the way you just explained.

I wish i could do it:

   from(i in Item,
      join: p in Product,
      where: ^where,
      order_by: ^order_by
    )

instead of

   from(i in Item,
      join: p in Product,
      where: ^where,
    )
    |> order_by(...)

Is there any option to do this?

1 Like

order_by accepts a list like …, order_by: ^list if you don’t need multiple bindings.

1 Like

list here cannot reference ecto bindings so he cannot sort on the second table this way I think. There is a field macro, that could maybe be used to do this but I find it far more straightforward to compose on the queryable itself.

1 Like

Thank you guys. I indeed have multiple bindings, I had a look at field, but it doesn’t look to be documented in Ecto.Query page. anyway trying to use it field(i, asc: i.something) complaint i does not exist

Everything you use within queries is documented in Ecto.Query.Api.

1 Like
order_by =
  case params["order_by"] do
    "price_asc" -> [asc: :price]
    "price_desc" -> [desc: :price]
    _ -> [desc: :released_at]
  end

query =
  from(i in Item,
    join: p in Product,
    where: ^where,
    order_by: ^order_by
  )

might work - but if it works, it only works for the first queryable in the list.


https://hexdocs.pm/ecto/Ecto.Query.html#order_by/3-keywords-examples

from(c in City, order_by: [:name, :population])
from(c in City, order_by: [asc: :name, desc_nulls_first: :population])

… only saw this later …

I don’t think this works for queryables with a join does it?

See: Dynamic field source for order_by

In that example you are sorting by a field in the first table. Where the guy ended up was quite different. He needed to be able to order by fields in multiple tables too.

Which I stated as a limitation above.

The final solution there was along the lines of your suggested solution which is more generally applicable:

query = from(i in IThing, join: p in assoc(i, :pthing))
        |> order_query(params["order_by"])

# ...

def order_query(query, "price_asc"),
  do: order_by(query, [i, p], asc: i.price)

def order_query(query, "price_desc"),
  do: order_by(query, [i, p], desc: i.price)

def order_query(query, _),
  do: order_by(query, [i, p], desc: i.released_at)
1 Like

Little Ecto Cookbook has similar example of using dynamic with order_by

def filter(params) do
  Post
  |> order_by(^filter_order_by(params["order_by"]))
  |> where(^filter_where(params)) 
end

def filter_order_by("published_at_desc"), do: dynamic([p], desc: p.published_at)
def filter_order_by("published_at"), do: dynamic([p], p.published_at)
def filter_order_by(_), do: []

but it raises the same error.

Good catch! It should be:

def filter(params) do
  Post
  |> order_by(^filter_order_by(params["order_by"]))
  |> where(^filter_where(params))
end

def filter_order_by("published_at_desc"),
  do: [desc: dynamic([p], p.published_at)]

def filter_order_by("published_at"),
  do: [asc: dynamic([p], p.published_at)]

def filter_order_by(_),
  do: []

I have fixed the guides and the book in master.

2 Likes

Is the repo for the book public somewhere? Google is coming up empty. I just downloaded the guide / PDF and the unfixed version is still being served.

The book is extracted from the guides in the ecto repository. So it has been fixed in master but not published.