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)

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.

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

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

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.