Drizzle Team•4mo ago

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)

2 Replies

Perny•4mo ago

Here is my query:

const result = await db
    .select()
    .from(game)
    .where(eq(game.id, "g_123"))
    .leftJoin(goal,
        eq(game.id, goal.game_id)
    )

const result = await db
    .select()
    .from(game)
    .where(eq(game.id, "g_123"))
    .leftJoin(goal,
        eq(game.id, goal.game_id)
    )

But it only returns it as one-to-one

Perny•4mo ago

The documentation mentions an .all() method but that does not appear to exist. The snippet is also quite cursed mentioning variables that don't exist and not containing all imports.

Drizzle ORM - Joins

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

Gaming

Programming

many-to-one transaction with select API and Postgres