I have a query like: Alarms |> preload(:event) |> order_by([{:desc, :event[:id]}]) |> Repo.all() You can see what I am trying to do, reach into the preloaded event and order the alarms by the id field within the event field / association. I feel I am very close to achieving this, but I also su…

This is the join solution I came up with. Not too elegant.. Alarms |> join(:left, [a], e in assoc(a, :event)) |> order_by([a, e], [{:desc, e.id}]) |> select([a, e], {a, e}) |> Repo.all() Unsure how I can generalise this, and reuse it..

Not too elegant… Unsure how I can generalise this, and reuse it… It’s not exactly clear what kind of “reuse” (or elegance) you are after.

E.g. dropping in a list of things to order by is nice: [image] mmport80: [{:desc, :event[:id]}] versus this: [image] mmport80: |> order_by([a, e], [{:desc, e.id}]) cannot just drop in these args.. Etc..

It’s easy enough to write a function that produces an “order by” that can vary dynamically. “Dropping in a list” doesn’t really seem that useful given how tightly that information is coupled to the table schemas. Are you pursuing a particular usecase?

An API with multiple endpoints, where you could create an endpoint and drop in which fields can be / should be ordered / filtered by… It’s worked well so far, these nested associations are irksome though. I have found an API in Ecto which looks similar to what I need, just not available for order_…

So your issue is that you can’t order preloads? Keep in mind that associations are entirely an Ecto concept that has nothing to do with the relational capabilities of SQL and “ORDER BY” is part of the SQL aspect.

Thanks - I looked at those. I understand preload queries can order the data within the association… But I am looking to order the whole result set, depending on what you find in the ‘belongs_to’ association.

Ah. You can tweak what you have a little I think and do: Alarms |> join(:left, [a], e in assoc(a, :event)) |> order_by([a, e], [{:desc, e.id}]) |> preload([a, e], event: e) |> Repo.all() In any case, doing the join so that you can order seems like exactly the right way to do the SQL.

Sort Ecto Query Results Ordered by Nested Association

Questions & Help Questions

benwilson512 December 10, 2018, 1:14pm 9

Check out preload queries, they’re perfect for this: Ecto.Query — Ecto v3.14.0

How to join multiple models in on ecto query