Many-to-many association table with extra columns

[cmkarlsson]

Is there a good way using ecto to add a many-to-many association with additional data needed.

The table structure is something like

create table(:types) do
  add :name, :string
end

create table(:events) do
  add :name, :string
end

create table(:event_types) do
  add :event_id, references(:events)
  add :type_id, references(:types)
  add :quantity, :integer
end

In the association table I have an extra column quantity which I need populated.

Without the quantity I can simply use put_assoc/3 to let ecto insert the associations.

Something like:

event
|> cast(params, [@fields])
|> put_assoc(:types, parse_types(params))


def parse_types(params) do
  Type 
  |> where([t], t[id] in ^params["type_ids"])
  |> Repo.all()
end

But how can I manage to fill in the quantity of the association table? Or are there any other idiomatic way to handle this in ecto?

[cmkarlsson]

To reply to my own question. Instead of a many_to_many relationship I
can model this as a has_many against the association table.

Then I can use put_assoc/3 with the quantity and a
foreign_key_constraint against the “types” table.

[OvermindDL1]

@cmkarlsson

To reply to my own question. Instead of a many_to_many relationship I
can model this as a has_many against the association table.

Then I can use put_assoc/3 with the quantity and a
foreign_key_constraint against the “types” table.

Yep, that is what needs to be done.

I almost never see a many-to-many association table without extra data so it surprises me that the many_to_many in Ecto does not support it some how.

Well, I ‘do’ see empty association tables sometimes (not really ever in my systems, not even in this ancient DB either), but that is usually because the author has not normalized their data properly.

[Meshu]

Hey guys,

Thank you for the conversation !
I was also looking for the “right” way to model this and I must admit I am a bit sad that I can’t do it through the m2m api.
If ever you find a better way don’t hesitate to put it back here.

[bryansray]

I don’t suppose you have a link to code that shows your new has_many relationship against the association table? I too found it surprising that many_to_many apparently doesn’t support adding additional columns.

Thanks in advance.

[cmkarlsson]

I don’t have a link as such. I think I used the ebook here a lot: http://pages.plataformatec.com.br/ebook-whats-new-in-ecto-2-0

But basically you have 3 tables. The two “main” tables and an association table. So assume the main tables are “person” and “event”. 1 Person can have many events and one Event can have many versions. They should also have a confirmed field to see if they have confirmed the event or not.

The schemas would look something like this:

schema "person" do
   field :name, :string
   has_many :events, PersonEvent, [on_replace: :delete] % PersonEvent is the module for the association table struct
end

schema "event" do
    field :name, :string
    has_many :people, PersonEvent, [on_replace: delete]
end

schema "person_events" do
   belongs_to :event, Event, references: :event_id
   belongs_to :person, Person, references: :person_id
   field :confirmed, :bool

I quite frequently insert directly into the person_events table, but otherwise just use the schemas as a normal has_many.

[dmacewen]

@herdibintang posted an example a while back using belongs_to with through. I would love to see this baked into many_to_many though.

[LostKobrakai]

You can have both side-by-side already. A many_to_many assoc. for linking directly and a has_one / has_many assoc. to link to the join table’s schema, where you can check for the additional fields.

[type1fool]

I was struggling to get the additional column data from my many_to_many table, and this helped a lot. Thank you @cmkarlsson!

---

You can have both side-by-side already. A many_to_many assoc. for linking directly and a has_one / has_many assoc. to link to the join table’s schema, where you can check for the additional fields.

@LostKobrakai Is there any benefit to using both many_to_many and has_many in a schema? It seems redundant.

[thojanssens1]

@bryansray

I too found it surprising that many_to_many apparently doesn’t support adding additional columns.

This is the case in other ORM frameworks too, so seems normal.
For example from Doctrine ORM (PHP) docs:

Why are many-to-many associations less common? Because frequently you want to associate additional attributes with an association, in which case you introduce an association class. Consequently, the direct many-to-many association disappears and is replaced by one-to-many/many-to-one associations between the 3 participating classes.

[addstar]

I’m stuck in a similar situation where my join table has extra attributes. I was using many_to_many but have now switched to has_many

But I’m unsure how I can insert into the job table at the time of inserting into one of the other tables.

Here’s my schemas:

schema "users" do
  field(:email, :string)
  field(:password, :string)

  has_many :users_profiles, UserProfile
  has_many :profiles, through: [:users_profiles, :profiles]
end

schema "profiles" do
  field(:description, :string)
  field(:is_active, :boolean)

  has_many :users_profiles, UserProfile
  has_many :users, through: [:users_profiles, :users]
end

schema "users_profiles" do
  field(:role, :string)
  belongs_to(:user, User)
  belongs_to(:profile, Profile)
end

How can I insert a new profile for an existing user while inserting into the users_profiles with the role field set to “owner”?

@cmkarlsson said to use put_assoc/3 for his use case but I’m just inserting a single record and put_assoc/3 is for working with the whole collection.

[mekusigjinn]

Oh I think in this case, it’s exact case noted in put_assoc, where it is recommended you use something other than put_assoc: https://hexdocs.pm/ecto/Ecto.Changeset.html#put_assoc/4-example-adding-a-comment-to-a-post

I was facing your exact problem - in my case adding new address for a user.
Eventually maybe I’ll add address checks to find out same address was already created - however for now, my plan is to always add new addresses.

So that follows your example - I have users, addresses, and users_addresses as tables.

After working many strategies, I think the best case would be to be more explicit than be implicit in this case, which follows along the examples listed in the link in ways to insert a single new entry. Note that the examples in the link is not using a many-to-many table.

In my case, I decided to explicitly do the actual DB operations wrapped in Ecto.Multi. I think this is a case where cast_assoc and put_assoc can’t help, and I don’t think there are any helper functions that deal with adding extra columns in the many-to-many table. So, my strategy was if I at least be slightly lower level and explicitly drive the DB operations, at least it will be more maintainable later on.

Beauty of Elixir is that… well lemme show you the code first:

    profile = Repo.one(Profile)
    my_role = "some role"

    result =
      Ecto.Multi.new()
      |> Ecto.Multi.insert(:user, User.changeset(%User{}, address))
      |> Ecto.Multi.insert(:user_profile, fn %{user: user} ->
        %UserProfile{user_id: user.id, profile_id: profile.id, role: my_role}
      end)
      |> Repo.transaction()

    case result do
      {:ok, %{user_profile: user_profile}} ->
        {:ok, user_profile}

      {:error, _failed_operation, failed_value, _changes_so_far} ->
        {:error, failed_value}
    end

These few lines chain insertions, and also do transaction rollback. Amazing!
I’m still learning Ecto so there’s probably better ways to do it - but I tend to rather be close to the DB (as Ecto is still new for me)