Schema that needs to joins twice from the same schema

Right now I have a schema that is like this:

export const games = sqliteTable("games", {
  id: text().primaryKey().$defaultFn(v4),
  redTeamId: text().references(() => teamScores.id),
  blueTeamId: text().references(() => teamScores.id),
}, (ts) => ({
  teamUniqueIdx: unique("team_unique_idx").on(ts.redTeamId, ts.blueTeamId),
}));

export const games = sqliteTable("games", {
  id: text().primaryKey().$defaultFn(v4),
  redTeamId: text().references(() => teamScores.id),
  blueTeamId: text().references(() => teamScores.id),
}, (ts) => ({
  teamUniqueIdx: unique("team_unique_idx").on(ts.redTeamId, ts.blueTeamId),
}));

` The problem is that I cannot retrive the whole data, because if I try to join it in using the code below, it shows the message in the image

const game = await db.select()
      .from(games)
      .leftJoin(teamScores, eq(teamScores.id, games.redTeamId))
      .leftJoin(teamScores, eq(teamScores.id, games.blueTeamId))
      .then(firstOrNull);

const game = await db.select()
      .from(games)
      .leftJoin(teamScores, eq(teamScores.id, games.redTeamId))
      .leftJoin(teamScores, eq(teamScores.id, games.blueTeamId))
      .then(firstOrNull);

Is there a way to have that type of structure or am I limited to 1 join per query?

4 Replies

Sillvva•3mo ago

When joining the same table multiple times, you need to give the table an alias. https://orm.drizzle.team/docs/joins#aliases--selfjoins

const redTeamScores = alias(teamScores, "redTeamScores");
const blueTeamScores = alias(teamScores, "blueTeamScores");
const game = await db.select()
      .from(games)
      .leftJoin(redTeamScores, eq(redTeamScores.id, games.redTeamId))
      .leftJoin(blueTeamScores, eq(blueTeamScores.id, games.blueTeamId))
      .then(firstOrNull);

const redTeamScores = alias(teamScores, "redTeamScores");
const blueTeamScores = alias(teamScores, "blueTeamScores");
const game = await db.select()
      .from(games)
      .leftJoin(redTeamScores, eq(redTeamScores.id, games.redTeamId))
      .leftJoin(blueTeamScores, eq(blueTeamScores.id, games.blueTeamId))
      .then(firstOrNull);

Drizzle ORM - Joins

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

SunOP•3mo ago

... drizzle is more and more complex everytime I try to use it, lol wait, why do I need to import that from each core? is there no "unique api" for that?

Sillvva•3mo ago

Not really sure about imports, but the alias conflict is actually a SQL thing, not drizzle. This is postgres, but I imagine sqlite has the same issue. This is why you need aliases.

SunOP•3mo ago

I just ended up like, rolling up my own stuff with knex drizzle just made my life a bit worse here, sry

Gaming

Programming

Schema that needs to joins twice from the same schema

Did you find this page helpful?