Many to Many - Getting A and B where B.id =

I’d keep the subquery but the query would be:

from(Game, as: :game)
|> join(:inner, [game: g], ug in assoc(g, :users_games), as: :user_game)
|> join(:inner, [user_game: ug], u in assoc(ug, :users), as: :user)
|> where([game: g], g.id in subquery(subquery))
|> preload([user: u], users: u)
|> Repo.all()

In the end, you want a list of games so in order to avoid distinct, select a list of games, not users. By preloading in the query expression, you avoid an additional query (Repo.preload).

I used the more verbose version of the Query DSL in order to be more explicit about the bindings.

No need for subquery, I think, but I’d check the execution plans to pick the best query.

query =
  from game in Game,
    join: users_game in assoc(game, :users_game),
    where: users_game.user_id == ^user_id,
    join: user in assoc(game, :user),
    preload: [users: user]

Repo.all(query)

Someone correct me if I’m wrong but I’m pretty sure using a subquery in your query technically counts as 2 queries in postgres.

Either way, I wouldn’t worry so much if ecto spits out 1 query or multiple if I was a beginner. Just do the simple:

Repo.all(Game) |> Repo.preload(:users) assuming the Game schema has many_to_many :users, User, join_through: UsersGames.

The 1 query version would be:

query =
  from g in Game,
    join: ug in UsersGames,
    on: g.id == ug.game_id,
    join: u in User,
    on: ug.user_id == u.id,
    preload: [users: u]

Repo.all(query)

Your examples don’t filter the games in any way. OP wants to list the games a particular user has played and all the other player is those games.

I believe this query will return only one user for every game. Putting where in between the joins won’t make any difference as SQL will order the directives the right way.

I might be wrong, of course.

No, it won’t. Database will return a table where games and users tables are joined, and then Ecto will group them up by games.id and users will be accumulated into users field (thats how preload works in Ecto)

I’ve tested it out in sample project and something like this was returned. Users 1 to 5 were playing in games from 1 to 5 and users 6, 7 and 99 joined the game 5 too.

[
  %Welp.Game{
    __meta__: #Ecto.Schema.Metadata<:loaded, "games">,
    id: 1,
    users: [
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 1,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 2,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 3,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 4,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 5,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      }
    ]
  },
  %Welp.Game{
    __meta__: #Ecto.Schema.Metadata<:loaded, "games">,
    id: 2,
    users: [
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 1,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 2,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 3,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 4,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 5,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      }
    ]
  },
  %Welp.Game{
    __meta__: #Ecto.Schema.Metadata<:loaded, "games">,
    id: 3,
    users: [
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 1,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 2,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 3,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 4,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 5,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      }
    ]
  },
  %Welp.Game{
    __meta__: #Ecto.Schema.Metadata<:loaded, "games">,
    id: 4,
    users: [
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 1,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 2,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 3,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 4,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 5,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      }
    ]
  },
  %Welp.Game{
    __meta__: #Ecto.Schema.Metadata<:loaded, "games">,
    id: 5,
    users: [
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 1,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 2,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 3,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 4,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 5,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 6,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 7,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      },
      %Welp.User{
        __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
        id: 99,
        games: #Ecto.Association.NotLoaded<association :games is not loaded>
      }
    ]
  }
]

Exactly right, I did it for readability.

True. I misunderstood.

To filter the games by user_id, this seems to work:

games =
  from g in Game,
    join: ug in UsersGames,
    on: [game_id: g.id, user_id: 1]

query =
  from g in games,
    join: u in assoc(g, :users),
    preload: [users: u]

Repo.all(query)

Ecto comes out with 1 query. No sub query:

SELECT g0."id", g0."name", g0."inserted_at", g0."updated_at", u3."id", u3."name", u3."inserted_at", u3."updated_at"
FROM "games" AS g0
INNER JOIN "users_games" AS u1 ON (u1."game_id" = g0."id") AND (u1."user_id" = 1)
INNER JOIN "users_games" AS u2 ON g0."id" = u2."game_id"
INNER JOIN "users" AS u3 ON u2."user_id" = u3."id" []

edit:

This can refactor down to:

query =
  from g in Game,
    join: ug in UsersGames,
    on: ug.game_id == g.id,
    where: ug.user_id == 1,
    join: u in assoc(g, :users),
    preload: [users: u]

Repo.all(query)

Almost the same as the solution from @Asd but it doesn’t need an explicit :users_games assoc in the Game schema.

Excellent, I knew it is was sub-optimal which is as much an SQL as an ecto thing.

Reading that now actually looks lovely.

I know you know this but for those who don’t, you do have to be careful using this if you are doing it with a lot more data or more joins. It can end up in a massive table that needs to be sent over the wire then reduced in Elixir which gets slower and slower. One query isn’t always better!