How can I get nested data to be inside the parent object

Hey! I need to map some sports game data onto a databse. Here is my schema:

game table:
id,home_team_id,away_team_id

team table:
id, name, country

game table:
id,home_team_id,away_team_id

team table:
id, name, country

How can I fetch a game and get it's home and away teams using the select() API? This would be the optimal response:

[
  {
    "id": "123",
    "home_team": {
      "id": "123",
      "name": "Loerm ipsum",
      "country": "US"
    },
    "away_team": {
      "id": "456",
      "name": "Just text",
      "country": "UK"
    }
  }
]

[
  {
    "id": "123",
    "home_team": {
      "id": "123",
      "name": "Loerm ipsum",
      "country": "US"
    },
    "away_team": {
      "id": "456",
      "name": "Just text",
      "country": "UK"
    }
  }
]

4 Replies

균어•6mo ago

i think you can use like this

    const homeTeam = this.db.$with('home_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.homeTeamId
      ))
    )

    const awayTeam = this.db.$with('away_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.awayTeamId
      ))
    )

    const result = await this.db.with(homeTeam, awayTeam).select({
      id: gameTable.id,
      homeTeam: this.db.select().from(homeTeam),
      awayTeam: this.db.select().from(awayTeam),
    }).from(gameTable)

    const homeTeam = this.db.$with('home_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.homeTeamId
      ))
    )

    const awayTeam = this.db.$with('away_team').as(
      this.db.select().from(teamTable).where(eq(
        teamTable.id,
        gameTable.awayTeamId
      ))
    )

    const result = await this.db.with(homeTeam, awayTeam).select({
      id: gameTable.id,
      homeTeam: this.db.select().from(homeTeam),
      awayTeam: this.db.select().from(awayTeam),
    }).from(gameTable)

i have ways using sub query

Perny•6mo ago

Three queries.. idk...

균어•6mo ago

when it was possibly to use alias in left join i won't be 3 query but i searched in drizzle types i cannot find alias props in left join method or you can use sql``

Perny•6mo ago

Probably will have to use SQL stings. This is so basic though, you'd think the they would've thought about it

Gaming

Programming

How can I get nested data to be inside the parent object