DT
Drizzle Team•3d ago
Sparrow

how to model this query in drizzle

SELECT 
  tournaments.id, 
  tournaments.title, 
  tournaments.info, 
  tournaments.due_date, 
  tournaments.registration, 
  winner.name AS winner,
  runner_up.name AS runner_up,
  most_points.name AS most_points
FROM tournaments
INNER JOIN users AS winner ON tournaments.winner = winner.id
INNER JOIN users AS runner_up ON tournaments.runner_up = runner_up.id
INNER JOIN users AS most_points ON tournaments.most_points = most_points.id
WHERE tournaments.id = '45FobTao63vsRyc1N1';
SELECT 
  tournaments.id, 
  tournaments.title, 
  tournaments.info, 
  tournaments.due_date, 
  tournaments.registration, 
  winner.name AS winner,
  runner_up.name AS runner_up,
  most_points.name AS most_points
FROM tournaments
INNER JOIN users AS winner ON tournaments.winner = winner.id
INNER JOIN users AS runner_up ON tournaments.runner_up = runner_up.id
INNER JOIN users AS most_points ON tournaments.most_points = most_points.id
WHERE tournaments.id = '45FobTao63vsRyc1N1';
I currently have this: 
  const tmp = await db
    .select({
      ...getTableColumns(tournaments_table),
      // id: tournaments_table.id,
      winner: tournaments_table.winner,
      runner_up: tournaments_table.runner_up,
      most_points: tournaments_table.most_points,
    })
    .from(tournaments_table)
    .innerJoin(users_table, eq(tournaments_table.winner, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.runner_up, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.most_points, users_table.id))
    .where(eq(tournaments_table.id, params.id));
  const tmp = await db
    .select({
      ...getTableColumns(tournaments_table),
      // id: tournaments_table.id,
      winner: tournaments_table.winner,
      runner_up: tournaments_table.runner_up,
      most_points: tournaments_table.most_points,
    })
    .from(tournaments_table)
    .innerJoin(users_table, eq(tournaments_table.winner, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.runner_up, users_table.id))
    .innerJoin(users_table, eq(tournaments_table.most_points, users_table.id))
    .where(eq(tournaments_table.id, params.id));
but I get an error saying: 
Error: Alias "users" is already used in this query
Error: Alias "users" is already used in this query
2 Replies
Sparrow
SparrowOP•2d ago
using aliasedTable still doesn't work
TOSL
TOSL•2d ago
You need to alias the joins because they are the same table. Looks something like this 
const winner = aliasedTable(user, "winner")
const runnerUp = aliasedTable(user, "runner_up"

etc

// then you join will look like this

.innerJoin(winner, ....)
.innerJoin(runnerUP, ...)
const winner = aliasedTable(user, "winner")
const runnerUp = aliasedTable(user, "runner_up"

etc

// then you join will look like this

.innerJoin(winner, ....)
.innerJoin(runnerUP, ...)

Did you find this page helpful?