Many_to_many only add/remove from join table without creating record in related table

Questions / Help
1

For many_to_many relationships, how can I have cast_assoc only add or delete records in the join table?

If I have Post and User schema that are many to many through a join table PostUser, I want to only delete and add records to the PostUser table, but Ecto is trying to insert into the User table.

Users table has rows with ids [1, 2, 3, 4, 5]

PostUser join table has:
%{ post_id: 1, user_id: 1 }
%{ post_id: 1, user_id: 2 }
%{ post_id: 1, user_id: 3 }

Repo.get!(Post, 1)
|> Repo.preload(:user_ids) # %Post{ id: 1, user_ids: [1, 2, 3] }
|> cast( %{ "user_ids" => [ 2, 3, 4 ] } )
|> cast_assoc(:tag_ids)
|> Repo.update()

Ecto does this.

DELETE FROM PostUser WHERE post_id = 1 AND user_id = 1
INSERT INTO users ( id, ... ) VALUES ( 4, ... )
# Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.

I want Ecto to do this.

DELETE FROM PostUser WHERE post_id = 1 AND user_id = 1
INSERT INTO PostUser ( post_id, user_id ) VALUES ( 1, 4 )
2

Hi, Not sure if I am missing something but you are calling cast_assoc(:tag_ids). Should it be :user_ids?

1 Like
3

Yes, it should be |> cast_assoc(:user_ids). I started the example for this question as tag_ids and changed it to user_ids.

4

This is what I had to do.

blog = Repo.get!(Blog, 1) |> Repo.preload([:user_ids])
user_ids = blog.user_ids |> Map.filter(fn user_id -> user_id.id in [ 2, 3, 4 ] end)

Repo.get!(Post, 1)
|> Repo.preload(:user_ids)
|> put_assoc(:user_ids, user_ids)
|> Repo.update()

There’s a hack with put_assoc to update the join table from params without loading user_ids.

This may be the reason it’s not possible with cast_assoc.

josevalim commented on Jul 28

Yes, because allowing that would be a security risk. For example, imagine you have manager table which has many projects. If I can assign any random ID, I could get a project that does not long to this org and assign it to my manager. Depending on how the query is structured, then I get visibility in projects from other orgs.

There could be ways to limit this but the best is to close the attack vector altogether and simply not allow this feature via cast_assoc. You can associate them in other, more explicit, ways. :slight_smile: