Sort all items in one-to-many relationship

Questions / Help
1

I have one-to-many ChannelCategory to Channel relationship. I’ve been doing:

Repo.all(from cc in ChannelCategory, preload: [:channels], order_by: [{:asc, :title}])

So the channel_categories are sorted, but the channels are unsorted because they’re just preloaded. Which works fine but I’d like to sort the channels too.

My guess is that I do a subquery of Channel, with an order_by: [{:asc, :name}] and then perhaps add that subquery to the ChannelCategory query, not sure.

I’ve tried this query that contains a subquery:

    channel_query = from Channel, order_by: [{:asc, :name}]
    nested = Repo.all(from cc in ChannelCategory,
                        join: ch in subquery(channel_query),
                        on: cc.id == ^channel_query.channel_category_id,
                        order_by: [{:asc, :title}]
                      )

but getting this error for some reason:

key :channel_category_id not found in: #Ecto.Query<from c0 in MyApp.Channels.Channel, order_by: [asc: c0.name]>

even though the Channel I have

belongs_to :channel_category, MyApp.Multimedia.ChannelCategory

and Channel migration:

add :channel_category_id, references(:channel_categories, on_delete: :delete_all, type: :binary_id)

and channel_category_id is definitely there populated with data.

Hopefully I’ve demonstrated an effort to figure-out how to do this along with a sincere interest in learning.

Using Elixir 1.14.0 and Ecto 3.9.0

Michael

2

I only tried Ecto a bit but I know SQL and you can’t order a join. You have order the result by joined colums.

3

You also need to use the binding when referencing fields in a join. I.e ch.category_id. This is the source of your error message but you’ll also have issues like the above poster said

4

Another thing is you can use a custom query for your preload instead of just naming the field with an atom. This query can have the sort order you want.

5

The preload_order option to has_many sounds like it will do what you want.

2 Likes
6

That worked quite well, thank you.