Sort Ecto Query Results Ordered by Nested Association

Questions / Help
, ,
1

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?

2 Likes
2

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ā€¦

3

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
4

E.g. dropping in a list of things to order by is nice:

versus this:

cannot just drop in these argsā€¦ Etcā€¦

5

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
6

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ā€¦

7

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
8

Good point.

9

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

3 Likes
10

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.

11

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.

9 Likes
12

Nice little tweak there, clever.

13

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.

5 Likes
14

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.

4 Likes