Sort Ecto Query Results Ordered by Nested Association

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:

versus this:

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_by:

from(city in City, preload: :country, select: struct(city, [:country_id, :name, country: [:id, :population]]))

Instead you use it to specify fields to return..

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.

Good point.

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

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.