Perny Posts - Answer Overflow

Perny

•Created by Perny on 5/18/2024 in #help

many-to-one transaction with select API and Postgres

Hey! I am trying to make a many-to-one transaction with my Postgres database and the select API. Here are some of my table definitions

export const game = pgTable('game', {
    id: varchar('id', { length: 21 })
        .primaryKey()
        .default(sql.raw(`generate_pri('${ID_PREFIXES.game}')`)),
});

export const game_config = relations(game, ({ one, many }) => ({
    goals: many(goal),
}));

export const goal = pgTable('goal', {
    id: varchar('id', { length: 21 })
        .primaryKey()
        .default(sql.raw(`generate_pri('${ID_PREFIXES.goal}')`)),
    number: serial('number'),
    event_player_id: varchar('event_player_id', { length: 21 }).references(() => event_player.id),

    game_id: varchar('game_id', { length: 21 }).references(() => game.id),
});

export const goal_config = relations(goal, ({ one }) => ({
    game: one(game, {
        fields: [goal.game_id],
        references: [game.id]
    })
}));

export const game = pgTable('game', {
    id: varchar('id', { length: 21 })
        .primaryKey()
        .default(sql.raw(`generate_pri('${ID_PREFIXES.game}')`)),
});

export const game_config = relations(game, ({ one, many }) => ({
    goals: many(goal),
}));

export const goal = pgTable('goal', {
    id: varchar('id', { length: 21 })
        .primaryKey()
        .default(sql.raw(`generate_pri('${ID_PREFIXES.goal}')`)),
    number: serial('number'),
    event_player_id: varchar('event_player_id', { length: 21 }).references(() => event_player.id),

    game_id: varchar('game_id', { length: 21 }).references(() => game.id),
});

export const goal_config = relations(goal, ({ one }) => ({
    game: one(game, {
        fields: [goal.game_id],
        references: [game.id]
    })
}));

(I'm sending query in the next message)

3 replies

DTDrizzle Team

•Created by Perny on 3/12/2024 in #help

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"
    }
  }
]

6 replies

Gaming

Programming