Sorting/order by in related records defined by has_many

I’m working with a self-referential schema that represents menu items (where each menu item can multiple menu items as children). It’s a little trickier because it’s a legacy database table with a custom primary key, but here’s what the schema looks like:

@primary_key {:menu_item_id, :integer, autogenerate: false, source: :menu_item_id}
@derive {Phoenix.Param, key: :menu_item_id}

schema "menus_items" do
    field :id, :integer, [source: :menu_item_id]  # <--- seems redundant, but it exposes the primary key column as "id"
    field :parent_id, :integer, [source: :menu_item_parent]
    field :order, :integer, [source: :menu_item_order]
    field :label, :string, [source: :menu_item_label]
    field :slug, :string, [source: :menu_item_code]

    belongs_to :parent, Menuitem, foreign_key: :menu_item_id, references: :menu_item_parent, define_field: false
    has_many :children, Menuitem, foreign_key: :parent_id, references: :menu_item_id

  end

I’m able to preload the children (and the grandchildren) of a single menu item using a function in my context:

def get_menu!(slug) do
    Repo.get_by!(Menuitem, slug: slug)
    |> Repo.preload(children: [:children])
end

This is working beautifully, but the menu items are not sorted by the “order” column. I’ve been struggling to make sense of the Ecto docs on this part… how can I specify that the records should be sorted by the “order” column?

Thanks – I’m can’t seem to figure out the proper syntax.

Did you try Ecto.Query - Preload quries? Haven’t used it for grandchildren / self-referencing… but you can specify order_by.

See also Preload with order

1 Like

I’m still dogged by the syntax here… even the basic Ecto queries don’t provide many examples of the original SQL query used, so it’s hard to put the given examples into the proper context…

Here’s the furthest I’ve gotten… it’s close, but it’s only sorting the LAST of the associations, not the immediate children.

Repo.get_by!(Menuitem, slug: slug, parent_id: 0)
|> Repo.preload(children: [children: from(m in Menuitem, order_by: m.order)])

I just don’t see where you can add a “from” clause to the list.

You have to use from syntax (fairly identical to how you’d do a SQL join), that is not, it would be more like (typed up in this post, not tested, but close enough):

from(
  m in Menuitem,
  join: c in assoc(m, :children),
  where: m.slug == ^slug and m.parent_id == 0,
  order_by: [m.order, c.order],
  preload: [children: c]
) |> Repo.all()

For note, although a SQL server will often optimize an embedded select like what you are doing into a join, that is not guaranteed, you should always do an explicit join instead.

Thank you! I actually figured out the syntax and posted it in this related post:

For preloading 2 levels deep, the structure looked like this:

Repo.get_by!(Menuitem, slug: slug, parent_id: 0)
    |> Repo.preload([{:children, from(m in Menuitem, order_by: m.order)}, children: [children: from(m in Menuitem, order_by: m.order)]])

The syntax you provided is incredibly useful as a point of comparison. Thanks again.

1 Like