HOWTO Set Column Value on Join Table Record for Many-to-Many Association

Phoenix Forum Questions / Help
,
1

Hi all,

I’m trying to make the most out of the conveniences/automagic of the Phoenix/Ecto frameworks to handle database INSERTs wherever possible. However, I’m finding it a bit difficult in my case of using a many_to_many association.

To elaborate more:

  • I’m associating Users and Organisations via Memberships
  • Membership records also have a user_role column to specify the role of the user in relation to the associated organisation (e.g., creator, administrator, etc.)
  • When a new user signs up, the sign up form also provides a name field so the user can provide the name of their organisation.
    • This part is key because it means that the User form has nested inputs for Organisation
    • To illustrate, the struct returned by the form when the user clicks register looks like this:
%{
   "email" => "test@test.com",
   "password" => "elixirrocks", 
   "organisations" => %{"0" => %{"name" => "ACME CO."}}
}
  • With the associations set properly in the relevant Ecto schemas, this struct would insert properly into the database using the following set of operations:
    %User{}
    |> User.changeset(attrs)
    |> Repo.insert()

What I would like to do is set the user_role to a specific value (e.g., creator) somehow without changing the set of operations as shown above. In other words, is there a way to set the user_role somehow on the attrs that Phoenix/Ecto would pick up and store in the Membership record?

If not, how would I go about doing this? Would I need to separate these into three separate INSERTs:

  1. INSERT user
  2. INSERT organisation
  3. INSERT membership with user_role

Thanks for your help in advance!

1 Like
2

Hi all,

I wanted to share how I resolved this issue, which was not as trivial as I expected it to be. Thankfully, the Elixir community on Discord was able to guide me through a lot of this, so sending them a big thanks for all the help! Thank you!

There were a few things that I did not mention in my original post that complicated the matter. They were:

  1. Relying on Phoenix’s nested forms to handle many-to-many associations
  2. Using cast_assoc in the User.changeset to handle nested data
  3. NOT NULL CONSTRAINT on the user_role field on the membership table – the JOIN table

Generally speaking, the key was to step away from the conveniences of the Phoenix framework and take a more “step-by-step” approach. In summary, the approach was to:

  1. Breakdown the INSERTs for each table separately
  2. Use Ecto.Multi and Ecto.Repo.transaction() to execute the INSERTs as a single transaction
  3. Remove, or rather do not use, cast_assoc within any changeset function
  4. Manually set the user_role on the membership
  5. Create an embedded_schema to represent the form, extract relevant values for each table and cast those values as appropriate using the relevant changeset function

The birds-eye view of this solution can be seen by looking at the Multi Transaction below:

1  Ecto.Multi.new()
2  |> Ecto.Multi.insert(:user, User.registration_changeset(%User{}, attrs))
3  |> Ecto.Multi.insert(:org,  Organisation.changeset(%Organisation{}, %{name: org_name}))
4  |> Ecto.Multi.insert(:membership, fn %{user: user, org: org} ->
5      Membership.membership_changeset(%Membership{}, user, org, %{user_role: "creator"})
6  end)
7  |> Repo.transaction(

Some key notes for those who might need them…

cast_assoc failed because of NOT NULL CONSTRAINT on JOIN TABLE field

Originally, the User.registration_changeset was this way because I was trying to make use of Phoenix’s nested form handlers:

1  def registration_changeset(user, attrs, opts \\ []) do
2    user
3    |> cast(attrs, [:first_name, :last_name, :email, :password])
4    |> cast_assoc(:organisations, required: false )
5    |> validate_email()
6    |> validate_password(opts)
7  end

When I submitted the form, the database would throw the following error.
ERROR 23502 (not_null_violation) null value in column "user_role" violates not-null constraint

The culprit was Line 4, the cast_assoc, but digging deeper, it’s because of the many-to-many association between User and Organisation that was defined by this:

many_to_many :users, App.Accounts.User, 
  join_through: App.Organisations.Membership

As mentioned, the error was thrown by the database and that’s because cast_assoc automatically creates the INSERT for the JOIN TABLE. However, because user_role has a NOT NULL constraint, an error was thrown because user_role was not set. And from what I’ve learned, cannot be set when using cast_assoc. Hence the need for separate INSERTs.

I first tried relaxing the constraint by removing the NOT NULL from the user_role field in the database and used the same Multi Transaction above, I was not a fan of doing this because it risks data integrity. What ended up happening, however, was two records for an organisation and a membership were being created – a record each for cast_assoc and a record each for the INSERT in the Multi Transaction.

So, it showed me clearly that cast_assoc was not needed (and should not be used) at all if I were breaking down the operation into multiple INSERTs. It also forced me to look for another way (see below) and also enabled me to (happily) restore the NOT NULL constraint.

embedded_schemas instead of nested forms

This is not to knock what the Phoenix Framework has provided to make things more convenient for us via nested forms but to demonstrate when it should be used. When I say nested forms, I’m referring to this guide titled Polymorphic associations with many to many. In this guide, it details an example of a simple Todo List. Simple in the sense that there the JOIN TABLE does not have additional fields to be set, let alone a NOT NULL constraint on one of those fields. In my particular case, a nested form simply would not work.

Similarly, there’s an argument that modeling forms this way is coupling it too closely with our database schema, which can lead to other complications down the road. So, in general, decouple I suppose!

I could write more, but I think this sufficiently covers the main areas. If you have any questions, feel free to leave a comment.

Cheers!

3 Likes
3

Very good write-up! Thanks for the share! :023:

1 Like
4

What was the motivation for using many_to_many versus has_many :through? Seems like an echo of long ago in Rails, where we tried to make has_and_belongs_to_many with extra columns on the join table before through existed; it did not go particularly well.

5

Thanks for the question. I think the Rails comment has some value. However, from what I understand, regardless of whether I used has_many :through or many_to_many, cast_assoc is not able to satisfy the requirement of having to set a value on the :through association, specifically the user_role field. Unless I’m missing something.

As my original question did not spur any responses, I’m open to hearing other ideas now that we have a viable solution on the table for discussion. :slight_smile: