How to insert_all with many to many

Questions & Help Questions
1

Hi!

Let’s say I have a model user:

schema "user" do
  field :name, :string
  has_many(:user_company_assocs, UserCompanyAssoc)
  has_many(:companies, through: [:user_company_assocs, :company])
end

And a model user_company_assoc:

schema "user_company" do
  field :deleted, :boolean
  belongs_to(:company, Company)
  belongs_to(:user, User)
end

I want to insert_all with the association, like this:

[
  %{
    name: "User 1",
    company_ids: [1,2,3]
  },
 %{
    name: "User 2",
    company_ids: [4,2]
 }

I tried with multi:

Ecto.Multi.new()
|> Ecto.Multi.insert_all(:users, User, changesets)
|> Ecto.Multi.insert_all(:company_assocs, UserCompanyAssoc, &generate_assoc_changesets/1))
|> DB.PG.Repo.transaction

But when it reaches to the second insert_all, I lose the company_ids because it is not recorded in the user table. The user names are saved and I have an array of users with the name and ids, but since the company_ids is just used on the generate_assoc_changesets, it looses the conection with each user. I still have access to the full payload with the company_ids, but don’t have the new users ids to create the right associations.

How can I solve this?

Thanks!

2

insert_all cannot handle associations unfortunately. The alternative I can think of would be to Enum.each() and use a regular insert.

I’m interested if anyone has other methods to accomplish this however.

Edit: In your case it would just be inside of Multi.run, if you needed to access the results of the previous multi operation.

Edit edit: Maybe some sort of batch_id at the top of your run operation so you can link them later?

3

Thanks for the reply @dbaer!

I read in the docs that insert_all has no support for associations, but I think it is only automatic assocs with put_assoc on the changeset. In my case I’m trying to manually create the associations with multi operations. So it shouldn’t be a limitation.

As far as I understood Multi.run and Multi.insert_all, both have access to the previous previous operations. Like this:

Ecto.Multi.new()
|> Ecto.Multi.insert_all(:users, User, changesets)
|> Ecto.Multi.insert_all(:company_assocs, UserCompanyAssoc, fn %{users: users} -> ... end)

Could you provide an example of this batch_id with run? I didn’t understand this solution.

Thanks again for the help.

4

Apologies I slightly misunderstood your original query. I’m not terribly familiar with many to many, but https://medium.com/coletiv-stories/ecto-elixir-many-to-many-relationships-66403933f8c1 should point you in the right direction, and it looks like you need to you many_to_many there instead of has_many.

5

Did you find a solution to your problem? I am having the exact same issue with nested insert_all

6

Yes. But I had to do it manually.
Using the same example from the question, i have this:

[
  %{
    name: "User 1",
    company_ids: [1,2,3]
  },
  %{
    name: "User 2",
    company_ids: [4,2]
  }
]

First I save the users, and the database returns the users ids:

[1, 2]

Then I merge the ids in the same order on the original inputs:

[
  %{
    id: 1,
    name: "User 1",
    company_ids: [1,2,3]
  },
  %{
    id: 2,
    name: "User 2",
    company_ids: [4,2]
  }
]

With that I can format the values for the second insert_all. I have the ids from the users and the ids from the companies.

All the records that you pass to insert_all, will be returned in the same order.

Important to note that insert_all doesn’t work well with changesets. So you will have to handle everything with raw data. In my case was worth it.

If you need code samples on how I did it, let me know.

7

Thank you