[Solved] Force update of many_to_many relations when a list is identical except for its order


Following my last question ([Solved] Nilify many-to-many relationships on update) about :on_replace behaviors, my application is nearing completion. Thanks again to those who helped.

I’ve been suprised by a behavior desired by users that I did not anticipate : It seems that when a model has a one-to-many or many-to-many relationship to another, ecto is really clever in calculating the changes.

If model A has a relation to the models Bs with ids [1,2,3,4,7] and that I pass in put_assoc the models Bs [1,7,2,3,4] (same models, different order), the join table isn’t flushed then re-inserted.

In other words, I relied on insertion order in join tables without thinking about it.
Is there a solution to force this delete/insert cycle of those entries without resorting to join schemas and an order column / manually deleting the entries when there’s a non-empty list as input of those relationships ?


I’m not sure i would rely on the system this way to handle ordering, but you could simply remove those id’s everytime you update the related models, forcing put_assoc to believe those are new entries, instead of updates to previous ones. You’ll have to set :on_replace to :delete in order to enforce this behavior (on the schema field having the relationship set) so that Ecto always removes the previous entries no longer available in the new provided set.

That will create new id’s everytime an update is made though. Not that this is a bad thing, but i’m not sure how Ecto handles inserting relationship (the order might not be guaranteed). I’d go with a column handling the ordering. You’d keep the same principle with :on_replace in this case, always providing the entire list, with updated ordering column when needed, missing ones that would automatically be removed, and new ones automatically added.

Hello, thanks for your answer.
I already have on_replace: :delete set, but it seems that Ecto detects that the data isn’t really replaced.

Here’s what i’m working on : I’m still struggling to generate the query, but the general principle is that put_assoc_force would detect a many_to_many relation and accordingly delete the entries, re-preload the relation on the model before calling the real put_assoc. I’d use this function instead of put_assoc where needed.

  def put_assoc_force(%{data: struct} = changeset, relation, content) do
    import Ecto.Query
    case apply(struct.__struct__, :__schema__, [:association, relation]) do
      %{cardinality: :many, join_through: table, join_keys: [owner, assoc]} ->
      {field, pk} = owner
      owner_pk = Map.get(changeset, pk)
    # how to generate this ? (from t in table, where: t.^(field) == ^owner_pk) |> MyApp.Repo.delete_all()
      changeset |> Textile.Repo.preload(:relation)
      _ -> changeset
    Ecto.Changeset.put_assoc(changeset, relation, content)

Well i’m not too proud of it, but this works :

  def put_assoc_force(%{data: struct} = changeset, relation, content) do
    import Ecto.Query
    c = case apply(struct.__struct__, :__schema__, [:association, relation]) do
      %{cardinality: :many, join_through: table, join_keys: [owner, assoc]} ->
      {field, pk} = owner
      owner_pk = Map.get(struct, pk)
      where_clause = Keyword.put([], field, owner_pk)
      (from table, where: ^where_clause) |> Textile.Repo.delete_all()
      Map.put(changeset, :data, Map.put(struct, relation, [])) # simulate preload of the now empty list
      _ -> changeset
    Ecto.Changeset.put_assoc(c, relation, content)

I’d not recommend following this approach if you have the opportunity of being aware of this behavior from the start.

I think you’re basically trying to replicate what Ecto will do automatically for you. When providing, on update, something like this (from the documentation):

put_assoc(changeset, :comments, [
  %{id: 1, title: "changed"}, 
  %{title: "new one"}

Here, Ecto will look on the preloaded data for the first comment with id == 1, and mark it for updating, if there’s any changes (on the title), and automatically create the second comment. Any other comments available on the preloaded data will be marked for deletion (because of the on_replace: :delete). Ecto always work with the primary key to match the data and knowing what to do (i’m using it myself).

One thing to note (still from the documentation). If you provided the relationship data directly has a changeset or struct (like this put_assoc(changeset, :comments, [%Comment{id: 1, title: "changed"}])), Ecto’s behavior changes. It will ignore the preloaded data, and only take care of ensuring the parent and child data are associated. So in this case, it’s mainly to associate already persisted data in your database to your parent table. That might be your issue, in which case you’d need to convert back the relationship data to a bare list of maps, remove the id’s and then call put_assoc, to enforce Ecto behavior the way you want.

I’d still go for an ordering column though.


It seems strange to me too, but my users reported that when you only change the order of already present data, nothing happens.

That is, when calling put_assoc like this :

put_assoc(changeset, images, [%{id: 1, foo: "bar"}, %{id: 2, foo: "baz"}, %{id: 3, foo: "dub"}])

if the preloaded list of images was (same IDs, different orders)

[%{id: 1, foo: "bar"}, %{id: 3, foo: "dub"}, %{id: 2, foo: "baz"}]

the update did not occur.

In this app (and maybe that’s a mis-use of Ecto on our side) put_assoc only gets existing data (it always gets created before being passed).

Edit : that’s exactly what your last reply said. The associations were present, so nothing happened. But i need to ensure those images are indeed unique. We’re running out of time, so I’ll use my hack for now. I hope the next update wave will allow us to change that.

Have a nice day !

To bring some light to the discussion:

Ecto doesn’t do any updates for changes only in order of assocs, because the db doesn’t retain said order. Unless you explicitly use queries with order_by for loading/preloading your assocs order will be indeterminate. To use order_by you need a field to order by, which you can update when the order is changed, which in turn will tell ecto to update something. Without that the order of assocs should be treated as essentially random.

For embeds on the other hand a change in order will make ecto detect the change, as embeds are stored in a way where order is retained in the db.


Indeed, I don’t have any guarantee that Postgres will continue to honor this behaviour (insert order === read order) in the future as it’s implicit. We should have planned better !

Iirc it’s even order of last update, not order of last insert.

1 Like

Thanks for this precision. I never update entries in those join tables, only insert or delete, but that makes me realize further the promiscuity of the situation.