Sort Ecto Query Results Ordered by Nested Association

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 suspect that it is not possible, and I need to use a join.

Any advice?

1 Like

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.

1 Like

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?

1 Like

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]]))

https://hexdocs.pm/ecto/Ecto.Query.API.html#struct/2

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.

1 Like

Good point.

Check out preload queries, they’re perfect for this: https://hexdocs.pm/ecto/Ecto.Query.html#preload/3-preload-queries

2 Likes

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.

7 Likes

Nice little tweak there, clever.

Order on each respective table to get a total order:

   albums_query =
      Album
      |> order_by([m], [desc: m.id])

    Artist
    |> join(:left, [a], m in assoc(a, :albums))
    |> order_by([a,m], [asc: a.id])
    |> preload([a,m], [albums: ^albums_query])
    |> Repo.all()

That being said associations by their nature have to stay clustered together.

3 Likes

Order gets overridden:

|> preload([a,m], :albums)

Order is respected

|> preload([a,m], [albums: m])

At this point I’m not even sure what the second one means … I blame macros as the difference seems a bit subtle (and as it should be unrelated).

Ok - preload/3:

  • [:albums] give me the whole bag unconstrained by any SQL constraints.
  • [albums: m] just give me those items in the bag that are in compliance with the SQL constraints.

The optional/vanishing list markers aren’t helping clarity either.

3 Likes