I had some trouble figuring out how to make many-to-many associations work. Once I got it working, I wrote a blog post. Because I’m a novice, I probably got some things wrong. Please correct me.
http://blog.roundingpegs.com/an-example-of-many-to-many-associations-in-ecto-and-phoenix/
Thats a good start at documenting the many_to_many macro.
If anyone can point the way, I’m looking for some description of how to use many_to_many in CRUD operations with change_sets.
The example you give is the way I am using many_to_many also. But if we need to manage the join table manually like that, I’m not seeing how this is any different that defining has_many on both sides.
Is there a developers guide somewhere that shows usage for the many_to_many macro?
from: https://hexdocs.pm/ecto/Ecto.Schema.html#many_to_many/3
It gives examples on how to get data back out.
# Get all comments for a given post
post = Repo.get(Post, 42)
tags = Repo.all assoc(post, :tags)
# The comments can come preloaded on the post struct
[post] = Repo.all(from(p in Post, where: p.id == 42, preload: :tags))
post.tags #=> [%Tag{...}, ...]
But what I’m completely stumped about is how to add, update, and delete data from a many_to_many.
Ok so as long as everyone understands that I’m also new to Phoenix/Ecto as well …
I’m working on an example / demo app to experiment with has_many and many_to_many relationships.
And of course it’s a blog. 
A Post has_many Comments
A Post will (someday be) many_to_many to Tag
Any comments and suggestions appreciated.
I have a variety of tests that I’ve done with many_to_many at Ecto `many_to_many` questions if they will help?
I think I’ve figured it out.
# Create the user. Note that the (empty) `organizations` field has to be preloaded.
user_map = %{display_name: "User 1", login_name: "1@y.com", password: "password"}
changeset = User.password_setting_changeset(%User{}, user_map)
user = Repo.insert!(changeset) |> Repo.preload(:organizations)
# Do the same for the organization:
org = %Eecrit.Organization{short_name: "org1", full_name: "Organization 1"}
org = Repo.insert!(org) |> Repo.preload(:users)
# Update one of the two of them:
changeset = Ecto.Changeset.change(user) |> Ecto.Changeset.put_assoc(:organizations, [org])
# When you save this change to the user, the join table will have its foreign keys populated in both directions.
Repo.update!(changeset)
Pretty slick.
But how to add one more organization then?
Update:
Well, it’s not so easy. The basic idea is that everything should be a changeset.
After creating new organisation
org_changesets = Enum.map([new_org|user.organisations], &Ecto.Changeset.change/1)
Ecto.Changeset.change(user) |> Ecto.Changeset.put_assoc(:organisations, org_changesets) |> Repo.update!
Hi everyone, sorry for being late to the discussion. If the documentation is incomplete, pull requests are always welcome! I have already improved those examples in the docs though. 
I have also removed the restriction that associated entries need to be changesets. We will now convert them implicitly.
I would recommend creating a join schema to handle many to many associations. This gives you the advantage of a unique changeset to update counter caching and whatnot.
This code is untested, and please forgive the Australian spelling of “Organisation”
The decoupled nature of Ecto allows us to do something like this…
defmodule App.UserOrganisation do
use App.Web, :model
@primary_key false
schema "user_organisation" do
belongs_to :user, App.User
belongs_to :organisation, App.Organisation
end
def changeset(struct, params \\ %{}) do
struct
|> cast(params, [:user_id, :organisation_id])
|> validate_required([:user_id, :organisation_id])
end
end
And from the other end…
defmodule App.User do
user App.Web, :model
schema "users" do
many_to_many :organisations, join_through: App.UserOrganisation
end
end
defmodule App.Organisation do
user App.Web, :model
schema "organisations" do
many_to_many :users, join_through: App.UserOrganisation
end
end
# Somewhere in your app
changeset = UserOrganisation.changeset(%UserOrganisation{}, %{user_id: id, organisation_id: id})
case Repo.insert(changeset) do
{:ok, assoc} -> # Assoc was created!
{:error, changeset} -> # Handle the error
end
I’ll take a look at the docs and see if this will suit a pull request
I’ve opened up a PR to add a similar example to the official docs
Perhaps I should open a fresh post on this matter - anyways, here goes:
I have a Jobber and a Job and I have a relation between them - surprisingly called JobberJob
schema "jobbers_jobs" do
belongs_to :jobbers, Jobbers.Jobber, foreign_key: :jobbers_id, references: :jobbers_id,type: :string
belongs_to :jobs, Jobbers.Job, foreign_key: :jobs_id, references: :jobs_id,type: :string
timestamps()
end
schema "jobs" do
field :jobs_id, :string, primary_key: true
....
many_to_many :jobbers, Jobbers.Jobber,
join_through: Jobbers.JobberJob,
join_keys: [jobs_id: :jobs_id, jobbers_id: :jobbers_id],
on_replace: :delete,
on_delete: :delete_all,
unique: true # this does not, however, make the jobbers DISTINCT
timestamps()
end
schema "jobbers" do
field :jobbers_id, :string, primary_key: true
field :name, :string
...
end
When I do: job = Repo.get_by(Job, id: 1) |> Repo.preload :jobbers this is what gets into my log:
[debug] QUERY OK source="jobs" db=0.7ms
SELECT j0.`id`, j0.`jobs_id`, j0.`teaser`, j0.`name`, j0.`job_when_name`, j0.`committee_name`, j0.`max_qty_jobbere`, j0.`min_qty_jobbere`, j0.`wanted_qty_jobbere`, j0.`inserted_at`, j0.`updated_at` FROM `jobs` AS j0 WHERE (j0.`id` = ?) [1]
[debug] QUERY OK source="jobbers" db=22.9ms decode=0.1ms
SELECT j0.`id`, j0.`jobbers_id`, j0.`name`, j0.`contact_address`, j0.`email`, j0.`mobile_clean`, j0.`workas_jobber`, j0.`state`, j0.`jobfunc`, j0.`create_date`, j0.`write_date`, j0.`birthdate`, j0.`country_code`, j0.`inserted_at`, j0.`updated_at`, j1.`jobs_id` FROM `jobbers` AS j0 INNER JOIN `jobs` AS j1 ON j1.`jobs_id` IN (?) INNER JOIN `jobbers_jobs` AS j2 ON j2.`jobs_id` = j1.`jobs_id` WHERE (j2.`jobbers_id` = j0.`jobbers_id`) ORDER BY j1.`jobs_id` ["__export__.campos_job_92"]
I suspect the “double-inner-join” to be the culprit behind providing TWO sets of jobbers - but I’m not able to disect it any better, I’m afraid 
I really could do with a “second opinion” - like someone telling me to follow this link or that, read this book og that; or perhaps just plain and simple stick it to me: this will not work in Ecto, currently!
cheers,
Walther
The two calls are a ‘good thing’ actually. 
Think of it this way, you get one query, and you have a N-to-Many mapping, so you join it to another and now instead of getting A amount of data, you are now getting A*B amount of data. By splitting it in to two queries when it preloads the system allows it to get A+B amount of data, which if there is many that can be called then it can result in substantially reduced amount of data requested, thus making the queries faster.
If however you tend to have a 1-to-1 mapping then that does not help and will indeed be a touch slower, but then you can just preload via a join instead of doing a naked preload and it is all responded at once.
I’m afraid I did not address the issue clearly
Being aware of the N+1 challenge - I added the |> Repo.preload :jobbers - and the log clearly shows that I’m dodging that bullet!
But that is not the issue! The issue is that the SQL generated is errant - in so far as I can tell 
It returns TWO sets of jobbers - “all I ever wanted was YO-- ONE” 
Oh, you are getting duplicates of the preloaded value? Hmm…
yep - and it’s because the SELECT has no DISTINCT (and because the ‘go-between’ table is included as a INNER JOIN) - my best guess
Hmm, I’m unsure why it is generating such a weird query for that preload, I’d never write one that way for a many-to-many join… (I don’t really use Ecto’s relations due to major limitations on them in the old system I have to work with). Maybe @michalmuskala or someone else can figure out the odd query generation?
thanks for the solution.
but what if the user.organisations is very big ?
Isn’t that only if you are putting in too much and keep your timeout too short? You should always set your timeout based on the data load that you are passing in.
Since your post only covers the creation of association, I have to ask.
If you don’t need a model for the inner table, how would you handle the removal of users_organizations?.
I’m kind of confused, since the use case I’m proposing is not destroying a user nor an organization, so I couldn’t do something like Repo.delete(%UserOrganization{})
One way might be to use ecto query
import Ecto.Query
"users_organizations"
|> where(user_id: ^user_id)
|> where(organization_id: ^organization_id)
|> Repo.delete()
or if you have the id of the pair
import Ecto.Query
"users_organizations"
|> where(id: ^users_organization_id)
|> Repo.delete()
© Copyright Elixir Forum | Terms | Privacy & Cookies
