Preload with order_by from another table with Ecto

Questions / Help
1

I’m trying to order some preload that are sent by the front end using ?include=. The issue that I’m having is when in a preload query, I try to join with other table (many_to_many) in order to order by a column in there.

I will copy/paste the question I’ve asked in stackoverflow. The solution that I end up go with was to eliminate the ?include= and provide a full query with all possible join needed, and use order_by([fc, r, fi, fiog, og, ogfe, fe], [fc.row_weight, fi.row_weight, fiog.row_weight]) to follow the order I want.

I think that are two scenarios for this issue:

  • I’m not using preload correctly.
  • There is a bug in preload code.

I want to get more feedback to check if it makes sensor to open an issue in ecto repository.

I have an issue with the order of some preloads.

Basically I have a structure like: Restaurant > FoodItem > OptionGroup.
An OptionGroup is associated to a Restaurant, and can be associated to a FoodItem (table with many_to_many FoodItemOptionGroup).

In the food_item.ex I have the following:

many_to_many(
  :option_groups,
  OptionGroup,
  join_through: FoodItemOptionGroup,
  on_delete: :delete_all
)

I allow the front end to query the API with the preloads they need, so they can do something like: ?include=food_item.option_groups. The function iterate and create a preload structure. This is the core part:

is_atom(value) ->
    case value do
      :food_items ->
        [{:food_items, from(p in FoodItem, order_by: [p.id])}]

      :option_groups ->
        [{:option_groups, from(og in OptionGroup, order_by: og.id)}]

      :food_extras ->
        [{:food_extras, from(fe in FoodExtra, order_by: fe.id)}]
    end

Recently I’ve implement an row_weight that allows me to order by it’s value. On FoodItem it was simple, just add order_by and it’s done. On OptionGroups, it’s been complicated. Because the row_weight is on the association many_to_many FoodItemOptionGroup, the preload doesn’t work properly.

is_atom(value) ->
    case value do
      :food_items ->
        [{:food_items, from(p in FoodItem, order_by: [p.row_weight, p.id])}]

      :option_groups ->
        [
          {
            :option_groups,
            
            OptionGroup
            |> join(
              :inner,
              [og],
              fiog in Ketchup.FoodItemOptionGroup,
              og.id == fiog.option_group_id
            )
            |> order_by([og, fiog], [fiog.row_weight, og.id])
          }
        ]

      :food_extras ->
        [{:food_extras, from(fe in FoodExtra, order_by: fe.id)}]
    end

This will result in multiple option_groups being duplicated by the number of associations they are to other food_items. It seams that preload pick only the right ones, for example Temperature and Spice and also take all the other Temperature and Spice (they share the same id because are the some) of other food_items.

There was a possible solution to use group_by, because I think it doesn’t affect the output OptionGroup.

OptionGroup 
|> join(:inner, [og], fiog in Ketchup.FoodItemOptionGroup, og.id == fiog.option_group_id) 
|> order_by([og, fiog], [fiog.row_weight, og.id]) 
|> group_by([og, fiog], [og.id, fiog.row_weight])

But will give the following error if run as a preload. If run as a standard query it does retrieve the right OptionGroups.

** (Postgrex.Error) ERROR 42803 (grouping_error): column "f2.id" must appear in the GROUP BY clause or be used in an aggregate function

In the first example, it looks like it need a filter by food_item_id, but the only way to do that is using function in preload.

fn food_items_ids ->
  OptionGroup
  |> join(:inner, [og], fiog in Ketchup.FoodItemOptionGroup,
      og.id == fiog.option_group_id and fiog.food_item_id in ^food_items_ids)
  |> order_by([og, fiog], [fiog.row_weight, og.id])
  |> Ketchup.Repo.all()
end

But in this scenario, what the preload will do is to only add the OptionGroup that have the same id as the FoodItem.

I think I’m out of options to try here, I’m thinking in probably right one query without options to select which preloads you want or not.

EDIT

This is the preload I’m sending to Ecto:

GET ../endpoint1?include=option_groups

[       
  option_groups: #Ecto.Query<from o in Ketchup.OptionGroup,
   join: f in Ketchup.FoodItemOptionGroup, on: o.id == f.option_group_id,
   order_by: [asc: f.row_weight, asc: o.id]>
]

I’m displaying here without distinct, but it distinct it will show all OptionGroups, but it will not order them because row_weight aren’t available in all of them, when I just want the one with the connection with FoodItem that have row_weight.

GET ../endpoint2?include=food_items.option_groups.food_extras

[       
  food_items: #Ecto.Query<from f in Ketchup.FoodItem,
   order_by: [asc: f.row_weight, asc: f.id]>,
  food_items: [
    option_groups: #Ecto.Query<from o in Ketchup.OptionGroup,
     join: f in Ketchup.FoodItemOptionGroup, on: o.id == f.option_group_id,
     order_by: [asc: f.row_weight, asc: o.id], distinct: [asc: o.id]>
  ]
]

EDIT2

So, I end up using a query. I will be trying to implement GraphQL in the future.

FoodCategory
|> join(:inner, [fc], r in Restaurant, r.id == fc.restaurant_id)
|> join(:inner, [fc, r], fi in Ketchup.FoodItem, fi.food_category_id == fc.id)
|> join(:inner, [fc, r, fi], fiog in Ketchup.FoodItemOptionGroup, fiog.food_item_id == fi.id)
|> join(:inner, [fc, r, fi, fiog], og in Ketchup.OptionGroup, og.id == fiog.option_group_id)
|> join(
  :inner,
  [fc, r, fi, fiog, og],
  ogfe in Ketchup.OptionGroupFoodExtra,
  ogfe.option_group_id == og.id
)
|> join(
  :inner,
  [fc, r, fi, fiog, og, ogfe],
  fe in Ketchup.FoodExtra,
  ogfe.food_extra_id == fe.id
)
|> where([fc], fc.restaurant_id == ^restaurant_id)
|> preload(
  [fc, r, fi, fiog, og, ogfe, fe],
  food_items: {fi, option_groups: {og, food_extras: fe}}
)
|> order_by([fc, r, fi, fiog, og, ogfe, fe], [fc.row_weight, fi.row_weight, fiog.row_weight])