Sorting a many_to_many relationship using an attribute of the join resource

Elixir Framework Forums Ash Forum
, , , ,
1

Hi everyone!

In my application, I’m trying to model a DailyMenu that has a many_to_many relationship with a Dish, through a DailyMenuDish join resource.

Since I want to maintain the order of dishes that the user passes when creating the DailyMenu, the DailyMenuDish resource also has a position attribute, which I was able to successfully populate on creation using manage_relationship with the 4-tuple on_no_match option.

This is a relevant bit of the join resource

  attributes do
    attribute :position, :integer do
      allow_nil? false
    end
  end

  relationships do
    belongs_to :daily_menu, DailyMenu, primary_key?: true, allow_nil?: false
    belongs_to :dish, Dish, primary_key?: true, allow_nil?: false
  end

And this is the relationship on the DailyMenu resource

  relationships do
    many_to_many :dishes, Dish do
      through DailyMenuDish
      source_attribute_on_join_resource :daily_menu_id
      destination_attribute_on_join_resource :dish_id
    end
  end

How can I ensure that when I load :dishes in a DailyMenu they are sorted by the position attribute defined in DailyMenuDish?

2

you can specify a sort on the relationship DSL: Ash.Resource — ash v2.16.0

3

What syntax should I use given that the :position attribute is not in the Dish resource (which is returned by the :dishes relationship) but on the DailyMenuDish join resource?

I already tried with a simple

sort [:position]

on the many_to_many relationship but that fails with Ash.Error.Query.NoSuchAttribute.

4

In this case you’d want to sort the join relationship.

  relationships do
    has_many :daily_menu_dishes, DailyMenuDish do
      sort [:position]
    end

    many_to_many :dishes, Dish do
      through DailyMenuDish
      join_relationship :daily_menu_dishes
      source_attribute_on_join_resource :daily_menu_id
      destination_attribute_on_join_resource :dish_id
    end
  end
5

This seems to work only on the has_many relationship

DailyMenu
|> FoodVoting.read!()
|> FoodVoting.load(:daily_menu_dishes)

Generates this SQL in the logs (and the :daily_menu_dishes have the correct order)

SELECT d0."position", d0."daily_menu_id", d0."dish_id" FROM "daily_menus_dishes" AS d0 WHERE (d0."daily_menu_id"::uuid IN ($1::uuid,$2::uuid,$3::uuid)) ORDER BY d0."position" ["5c1f1245-d8df-4b02-af42-8ffea66e900a", "83fe9ee6-ff6f-40c8-91d4-d8a521fd8bd1", "9c5d0469-f709-47cc-bd0b-9ff0bffd3dc5"]

while

DailyMenu
|> FoodVoting.read!()
|> FoodVoting.load(:dishes)

Generates this SQL in the logs

SELECT DISTINCT s1."id", s1."name", s1."inserted_at", s1."__lateral_join_source__" FROM "daily_menus" AS d0 INNER JOIN LATERAL (SELECT sd0."id" AS "id", sd0."name" AS "name", sd0."inserted_at" AS "inserted_at", sd1."daily_menu_id" AS "__lateral_join_source__" FROM "public"."dishes" AS sd0 INNER JOIN "public"."daily_menus_dishes" AS sd1 ON sd1."dish_id" = sd0."id" WHERE (sd1."daily_menu_id" = d0."id")) AS s1 ON TRUE WHERE (d0."id" = ANY($1)) [["5c1f1245-d8df-4b02-af42-8ffea66e900a", "9c5d0469-f709-47cc-bd0b-9ff0bffd3dc5", "83fe9ee6-ff6f-40c8-91d4-d8a521fd8bd1"]]

and the results are not sorted. I also tried loading both relationships (in the same or separate load) but the dishes never get sorted.

7

Ah, right. So, there is always the escape hatch of a manual relationship, which may be what you need to do in the short term. Join Manual Relationships — ash_postgres v1.3.10

I think we need to support the parent expression in sorts, which was easy to add. but I just tested it and we don’t support the ability to reference the join table. I think I can add that as well, but might not be able to get to it before I go on vacation. Let me see what I can do. Here is the syntax you’d use once I support it.

   require Ash.Sort
 
   many_to_many :dishes, Dish do
      through DailyMenuDish
      join_relationship :daily_menu_dishes

      sort [Ash.Sort.expr_sort(parent(daily_menu_dishes).position)]

      source_attribute_on_join_resource :daily_menu_id
      destination_attribute_on_join_resource :dish_id
    end
8

Okay, I’ve pushed preliminary support for using parent in that context. Would you mind trying it out? You’d need to try it on ash_postgres main branch.

1 Like
9

I used the source syntax from the tests you added in the commit, so

   many_to_many :dishes, Dish do
      through DailyMenuDish
      join_relationship :daily_menu_dishes

      sort [Ash.Sort.expr_sort(source(daily_menu_dishes.position))]

      source_attribute_on_join_resource :daily_menu_id
      destination_attribute_on_join_resource :dish_id
    end

and it works, thanks!

It didn’t work using the parent syntax btw, it returned Invalid reference parent.position.

This is the generated SQL for reference

SELECT DISTINCT s1."id", s1."name", s1."inserted_at", s1."__order__", s1."__lateral_join_source__" FROM "daily_menus" AS d0 INNER JOIN LATERAL (SELECT sd0."id" AS "id", sd0."name" AS "name", sd0."inserted_at" AS "inserted_at", row_number() OVER "order" AS "__order__", sd1."daily_menu_id" AS "__lateral_join_source__" FROM "public"."dishes" AS sd0 INNER JOIN "public"."daily_menus_dishes" AS sd1 ON sd1."dish_id" = sd0."id" WHERE (sd1."daily_menu_id" = d0."id") WINDOW "order" AS (ORDER BY sd1."position"::bigint) ORDER BY sd1."position"::bigint) AS s1 ON TRUE WHERE (d0."id" = ANY($1)) ORDER BY s1."__order__" [["5c1f1245-d8df-4b02-af42-8ffea66e900a", "9c5d0469-f709-47cc-bd0b-9ff0bffd3dc5", "83fe9ee6-ff6f-40c8-91d4-d8a521fd8bd1"]]
10

Ah, right, its parent_expr or source, not parent :slight_smile: