Ecto: how to join three tables?

Questions / Help
,
1

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
2

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)
13 Likes
3

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

1 Like
4

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.

5

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.

3 Likes
6

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
7

Hey,

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 user.user_team_role.team?

Thanks

8

Yeah I guess it’s a little less convenient to access than just have user.team.role… 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