Question about Ecto dynamic and sortby

I’m trying to add a sort feature to an API which allows the user to pass the field name to sort by. I figured I could use dynamics as per but I run into an issue when adding the sort order specifier. this works fine:

dynamic([q], field(q, ^String.to_atom(sortby)))

this however gives an error

dynamic([q], desc: field(q, ^String.to_atom(sortby)))

** (Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size

In order to get this working now I’ve used order_by instead of dynamic

order_by(query, [q], desc: field(q, ^String.to_atom(sortby)))

Which works, but I’d like to understand why the dynamic isn’t working for me, as it’s pretty much the same as in the documentation

There’s a section on order_by in the docs for dynamic:

Thanks for the link, it explains using dynamics for the field in an order_by. The example I am referring to uses a dynamic as the orderby clause. I still don’t understand why the example from the docs (see link in original post)

dynamic([p], desc: p.published_at)

throws an error in my code. Removing the desc however works as expected.

So the problem is that the keyword syntax is special to the order_by macro. dynamic allows you to capture sql (query…) expressions. desc: field is not a query expression on it’s own, though. The keyword syntax is just a convenience to use elixir syntax with order_by. If you need to put the direction in the dynamic use plain sql possibly with an fragment.

def dynamic_order_expr(field, :asc), 
  do: dynamic([q], fragment("? ASC", field(q, ^field)))

def dynamic_order_expr(field, :desc), 
  do: dynamic([q], fragment("? DESC", field(q, ^field)))
1 Like

That makes sense. Thank you. Does that mean the section from the docs I linked is incorrect?

Oh, that’s interesting. Maybe this is actually supposed to work (which I wouldn’t have expected). Please open an issue on ectos github, so either docs or the bug can be fixed.

1 Like