DT
Drizzle Team•10mo ago
Perny

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
균어
균어•10mo 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
PernyOP•10mo ago
Three queries.. idk...
균어
균어•10mo 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
PernyOP•10mo ago
Probably will have to use SQL stings. This is so basic though, you'd think the they would've thought about it

Did you find this page helpful?