Ecto: how to join three tables?

I’m currently trying to understand how to join three tables using Ecto. All the examples I’ve seen use 2, so maybe I’m just missing something.

  • I have 3 tables
    • users
    • teams
    • roles
  • The set of roles is a static set of 9 roles
  • A user belongs to many teams with a single role

If I was writing SQL by hand, I think the query would be something like:

FROM group_user_roles gur 
JOIN teams t
ON = gur.team_id
JOIN roles r
ON = gur.role_id
JOIN users u
ON = gur.user_id

Or something like that…

Does anyone know how would I go about achieving something like this through Ecto?

Thanks for any responses/views!


The function you’re looking for is this:

To write the same SQL you have would be something like:

Repo.all from gur in "group_user_roles", 
  join: t in "teams", on: == gur.team_id,
  join: r in "roles", on: == gur.role_id,
  join: u in "users", on: == gur.user_id

That would have the same result of the select, and it does not use any Ecto.Schema. If you have the schemas like:

defmodule GroupUserRole do
  schema "group_user_roles" do
    belongs_to :user, User
    belongs_to :role, Role
    belongs_to :team, Team

defmodule User do
  schema ...

defmodule Team do
  schema ...

defmodule Role do
  schema ...

You can do a simpler query:

Repo.all from gur in GroupUserRole, 
  join: t in assoc(:team),
  join: r in assoc(:role),
  join: u in assoc(:user)

@kelvinst thanks for the reply! i’ll give that a shot

1 Like

I had a different way of solving the same problem. I’m joining users to teams via a many-to-many through, and the through table holds roles and statuses.

schema "users" do
   field(:email, :string)
   field(:password, Comeonin.Ecto.Password)
   field(:verified, :boolean)

     join_through: "users_teams_roles"

 schema "teams" do
   field(:name, :string)

     join_through: "users_teams_roles",
     on_replace: :delete

   has_many(:users_teams_roles, UsersTeamsRoles)

 schema "users_teams_roles" do
   field(:user_id, :id)
   field(:team_id, :id)
   field(:role, UserTeamRoleType)
   field(:status, UserTeamStatusType)

And the query would look like:

    team_id = 123
    user_id = 321
           team in Team,
           join: user in User,
           join: user_team_role in UserTeamRole,
    == user_team_role.team_id and
    == user_team_role.user_id,
           where: == ^user_id and == ^team_id,
           select: %{
             user_team_role: %{role: user_team_role.role, status: user_team_role.status},
             current_user: user,
             current_team: team

For the enums, I’m using {:ecto_enum, “~> 1.0”} which is pretty great.

The quoted didn’t work for me (2021).
This did:

You can do a simpler query:

Repo.all from gur in GroupUserRole, 
  join: t in assoc(gur, :team),
  join: r in assoc(gur, :role),
  join: u in assoc(gur, :user)

Anyone know why? Syntax change? I am a newbie so pardon my question.


Sorry I took so long to answer. So basically it was not working for you because I typed my example straight in here and forgot to add the first argument of the assoc as you did. So that’s it, your version is actually the right one :smiley:

1 Like


I found your answer that seems relevant to my question here: From many to one to many to many with data on the join table

I am looking at a similar setup.
When you would want to show the user their teams, how do you go about that? Do you not miss having user.user_team_role.role and subsequently


Yeah I guess it’s a little less convenient to access than just have… but it’s more flexible. To be honest I forget where I was even using that code so I can’t quite remember how I liked accessing those fields.

GitHub - woylie/flop: Filtering, ordering and pagination for Ecto is an interesting library that enables queries on joins pretty easily. I’m not sure if that’d be relevant if you want to query things a little more conveniently.

1 Like