Sort all items in one-to-many relationship

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: == ^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:]>

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


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.

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

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.

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


That worked quite well, thank you.