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:

SELECT *
FROM group_user_roles gur 
JOIN teams t
ON t.id = gur.team_id
JOIN roles r
ON r.id = gur.role_id
JOIN users u
ON u.id = 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!

2 Likes

The function you’re looking for is this: https://hexdocs.pm/ecto/Ecto.Query.html#join/5.

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

Repo.all from gur in "group_user_roles", 
  join: t in "teams", on: t.id == gur.team_id,
  join: r in "roles", on: r.id == gur.role_id,
  join: u in "users", on: u.id == 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
  end
end

defmodule User do
  schema ...
end

defmodule Team do
  schema ...
end

defmodule Role do
  schema ...
end

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)
10 Likes

@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)

   many_to_many(
     :teams,
     Team,
     join_through: "users_teams_roles"
   )
 end


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

   many_to_many(
     :users,
     User,
     join_through: "users_teams_roles",
     on_replace: :delete
   )

   has_many(:users_teams_roles, UsersTeamsRoles)
 end

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

And the query would look like:

    team_id = 123
    user_id = 321
    from(
           team in Team,
           join: user in User,
           join: user_team_role in UserTeamRole,
           on:
             team.id == user_team_role.team_id and
             user.id == user_team_role.user_id,
           where: user.id == ^user_id and team.id == ^team_id,
           select: %{
             user_team_role: %{role: user_team_role.role, status: user_team_role.status},
             current_user: user,
             current_team: team
           }
         )
         |> MyApp.Repo.one()

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