如何在Drizzle ORM中查询多对多关联游戏并返回平台对象数组

针对你提到的多对多关联场景，要查询所有游戏并附带关联的平台对象数组，我们可以通过以下步骤实现：

先修正表定义与关系的语法问题 #

你的原始代码存在几处语法错误和不严谨的地方，先修正确保代码可运行：

• 补全关系定义的闭合括号
• 为中间表字段添加外键约束（可选但推荐）
• 修复PlatformsTable中不存在的slug字段引用

修正后的完整表与关系定义：

import { pgTable, uuid, varchar, text, smallint, uniqueIndex, relations } from 'drizzle-orm/pg-core';
import { db } from './your-db-connection'; // 替换为你的数据库连接路径

const GamesTable = pgTable(
    'games',
    {
        id: uuid('id').primaryKey().defaultRandom().notNull(),
        name: varchar('name', { length: 255 }).notNull(),
        backgroundImage: text('background_image').notNull(),
    }
);

const GamesRelations = relations(GamesTable, ({ many }) => ({
    gamesToPlatforms: many(GamesToPlatformsTable)
}));

const GamesToPlatformsTable = pgTable(
    'games_to_platforms',
    {
        gameId: uuid('game_id').notNull().references(() => GamesTable.id),
        platformId: smallint('platform_id').notNull().references(() => PlatformsTable.id),
    },
    (t) => ({
        uniqueIdx: uniqueIndex(`unique_idx`).on(t.gameId, t.platformId),
    })
);

const GamesToPlatformsRelations = relations(
    GamesToPlatformsTable,
    ({ one }) => ({
        game: one(GamesTable, {
            fields: [GamesToPlatformsTable.gameId],
            references: [GamesTable.id],
        }),
        platform: one(PlatformsTable, {
            fields: [GamesToPlatformsTable.platformId],
            references: [PlatformsTable.id],
        }),
    })
);

const PlatformsTable = pgTable(
    'platforms',
    {
        id: smallint('id').primaryKey().notNull(),
        name: varchar('name', { length: 255 }).notNull(),
        imageBackground: text('image_background').notNull(),
    },
    (platforms) => ({
        uniqueIdx: uniqueIndex(`unique_idx`).on(platforms.name),
    })
);

const PlatformsRelations = relations(PlatformsTable, ({ many }) => ({
    gamesToPlatforms: many(GamesToPlatformsTable),
}));

两种查询方案 #

方案1：Drizzle ORM嵌套预加载（代码层转换） #

利用Drizzle的关系预加载获取关联数据，再在代码中将中间表的平台数据转换为数组：

async function getAllGamesWithPlatforms() {
    // 查询所有游戏及关联的中间表+平台数据
    const rawGames = await db.query.GamesTable.findMany({
        with: {
            gamesToPlatforms: {
                with: { platform: true }
            }
        }
    });

    // 转换结构，提取platforms数组
    return rawGames.map(game => ({
        ...game,
        platforms: game.gamesToPlatforms.map(link => link.platform),
        gamesToPlatforms: undefined // 可选：移除中间表冗余数据
    }));
}

方案2：SQL聚合查询（数据库层直接生成数组） #

使用PostgreSQL的json_agg函数直接在数据库层面聚合平台数据，减少客户端处理逻辑：

import { sql } from 'drizzle-orm';

async function getAllGamesWithPlatforms() {
    return db.select({
        id: GamesTable.id,
        name: GamesTable.name,
        backgroundImage: GamesTable.backgroundImage,
        platforms: sql`json_agg(${PlatformsTable})`.as('platforms')
    })
    .from(GamesTable)
    .leftJoin(GamesToPlatformsTable, GamesTable.id.eq(GamesToPlatformsTable.gameId))
    .leftJoin(PlatformsTable, GamesToPlatformsTable.platformId.eq(PlatformsTable.id))
    .groupBy(GamesTable.id);
}

方案对比 #

• 方案1适合需要对中间表数据做额外处理（比如筛选特定平台）的场景，逻辑更灵活。
• 方案2性能更优，直接在数据库完成聚合，返回结果就是最终需要的结构。

内容的提问来源于stack exchange，提问作者BamBam22