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.
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…
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.