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

Question

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 have three schemas that form the many_to_many association: User, Organisation, and Membership
• To spell it out, Users have many Organisations via Memberships, and Organisations have many Users 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
  • I also do not want to expose the user_role field on the form. This is something I want the system to manage behind the scenes.
  • To illustrate, the struct returned by the form 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 will insert into the database using the following set of operations:

%User{}
 |> User.changeset(attrs)
 |> Repo.insert()

However, 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 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 execute this operation with three separate INSERTs (wrapped in a transaction):

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

Thanks for your help in advance!

question from:https://stackoverflow.com/questions/65922835/howto-set-column-value-on-join-table-record-for-many-to-many-association

Answer 1

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!