Ecto join and order_by with dynamic values

Questions / Help
,
1

Hey fellow Elixirians!

I am having trouble with joining and ordering, but with dynamic values. So basically I send a dynamic “asc” or “desc” and a dynamic field to the function and it needs to order according to whats given. The problem is, that you can’t really put dynamic values in the ecto queries… Or maybe I am doing it wrong. But in any case, here is my code:

def with_ordering(query, filters, orderable_fields) do
if Map.has_key?(filters, "order") and filters["order"] in @order_types do
  order_by =
    if Map.has_key?(filters, "order_by") and filters["order_by"] in orderable_fields do
      String.to_atom(filters["order_by"])
    else
      :inserted_at
    end

  from(
    ue in query,
    join: e in assoc(ue, :event)
    order_by: ["#{filters["order"]}": ^order_by]
  )
else
  query
end

end

Next to not being able to do certain things in the query, how can I order_by “e” with the dynamic value?

I am trying to achieve the following results dynamically:

from(
    ue in query,
    join: e in assoc(ue, :event)
    order_by: [asc: e.id]
  )

Any tips, best practices, tricks and hacks are appreciated!

2 Likes
2

https://hexdocs.pm/ecto/2.2.10/Ecto.Query.API.html#field/2
This is probably what you’re looking for.

1 Like
3

@LostKobrakai Yesss, This definitely helped! Now the only thing left is to make the “asc” and “desc” dynamic. Since I cannot use [ “#{filters[“order”]}”: field(e, ^order_field) ] in the query. Got any ideas on that?

4

You can create the order_by list outside of the query and interpolate the whole list:

values = [asc: :name, desc: :population]
from(c in City, order_by: ^values)

That should allow it to be dynamically generated.

5

@mbuhot Have tried that already, but there is no way I can acces the joined table field. Like this will not work:

values = [asc: field(c, :name)]
from(
    e in query,
    join: c in assoc(e, :city)
    order_by: ^values
  )
1 Like
6

Doesn’t …, order_by: [{^direction, field(e, ^field)}] work?

8 Likes
7

@LostKobrakai Yes! This is it! Strange that this way of writing it is not in the docs for order_by. Would be great to note that, unless it’s written somewhere else I don’t know of… But in any case, thanks!

Here is the final result and solution (according to my case):

order_field = String.to_atom(filters["order_by"])
direction = String.to_atom(filters["order"])
  from(
      ue in query,
      join: e in assoc(ue, :event),
      order_by: [{^direction, field(e, ^order_field)}]
    )
2 Likes