How do I access fields from association table in many_to_many relationships in Ecto/Phoenix?

Question

I have a Phoenix App that has a many_to_many association. In this app:

I have a User table:

schema "users" do
  field :username, :string
  many_to_many :organizations, Organization, join_through: "memberships"
end

and an organization table:

schema "organization" do
  field :org_name, :string
  many_to_many :members, Users, join_through: "memberships"
end

Finally, I have the memberships table:

schema "memberships" do
  field :role, :string
  belongs_to :organization, Organization
  belongs_to :user, User
end

My question: Is there any nice way to retrieve the role field from the membership object, and the associated objects in a single SQL query? I understand that I can query for the associations and the associated objects in two separate queries, but I was wondering if there is a cleaner way to do it.

AlexHv AlexHv · Accepted Answer · 2017-08-17T11:06:41

Would something like this help you ? I havn't tested it, but it shows you the idea of a preload query:

First add a has_many memberships field to your model so you can preload a user's memberships:

schema "users" do
  field :username, :string
  many_to_many :organizations, Organization, join_through: "memberships"
  has_many: :memberships, Membership # <- add this line
end

Then try a request as follow:

import Ecto.Query

organization_id = ...

members_query =
   from u in User,
   join: m in Membership,
   on: m.user_id == u.id and m.organization_id == ^organization_id,
   preload: [memberships: m]

query =
   from o in Organization,
   where: o.id == ^organization_id,
   preload: [members: ^members_query]

query |> YourApp.Repo.all

How do I access fields from association table in many_to_many relationships in Ecto/Phoenix?

1 Answers