如何从SQL查询返回嵌套字段形式的对象数组？（Node+Drizzle+PostgreSQL）

问题描述 #

我是SQL新手，不清楚解决当前问题的正确方法。我有events和labels两张表，二者通过id字段形成多对多关系，且已创建联结表。

我希望获取如下格式的数据：一个events数组，每个event对象包含labels字段，该字段是对应event的labels列表：

[
 {
  id: 'event1',
  labels: [
   { id: 'label1'},
   { id: 'label2'}
  ]
 },
 {
  id: 'event2',
  labels: [
   { id: 'label3'},
   { id: 'label4'}
  ]
 }
]

请问实现该需求的最佳方式是什么？应该使用子查询，还是分成两次查询后再合并结果？

我使用的技术栈是Node.js、Drizzle ORM和PostgreSQL，尝试过Drizzle的查询和选择语法，但未能得到所需结果。以下是我的Drizzle schema：

const events = pgTable('events', {
  id: uuid('id').primaryKey(),
  userId: uuid('user_id')
    .notNull()
    .references(() => authUsers.id, { onDelete: 'cascade' }),
  description: text('description').notNull(),
  dueDate: timestamp('due_date'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
})

const eventsRelations = relations(events, ({ many }) => ({
  eventsToLabels: many(eventsToLabels),
}))

const labels = pgTable('labels', {
  id: uuid('id').primaryKey(),
  label: text('label').notNull(),
  description: text('description'),
  userId: uuid('user_id').references(() => authUsers.id, {
    onDelete: 'cascade',
  }),
})

const labelsRelations = relations(labels, ({ many }) => ({
  eventsToLabels: many(eventsToLabels),
}))

const eventsToLabels = pgTable(
  'event_label',
  {
    eventId: uuid('event_id')
      .notNull()
      .references(() => events.id, { onDelete: 'cascade' }),
    labelId: uuid('label_id')
      .notNull()
      .references(() => labels.id, { onDelete: 'cascade' }),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.eventId, table.labelId] }),
  }),
)

最佳实现方式 #

1. 用Drizzle ORM的嵌套关联查询（推荐） #

Drizzle ORM原生支持多对多关系的嵌套查询，不需要手动写子查询或分两次请求。首先需要完善你的关系定义，让events能直接关联到labels：

修改eventsRelations，添加到labels的关联：

const eventsRelations = relations(events, ({ many }) => ({
  eventsToLabels: many(eventsToLabels),
  labels: many(labels, { through: eventsToLabels, relationName: 'event_labels' })
}))

可选：给labels也添加到events的关联（如果后续需要反向查询）：

const labelsRelations = relations(labels, ({ many }) => ({
  eventsToLabels: many(eventsToLabels),
  events: many(events, { through: eventsToLabels, relationName: 'event_labels' })
}))

之后就可以用findMany配合with参数查询嵌套的标签数据：

import { db } from './your-db-connection'; // 替换为你的数据库连接实例

const eventsWithLabels = await db.query.events.findMany({
  with: {
    labels: {
      columns: { id: true } // 只返回label的id，匹配你需要的格式
    }
  },
  // 可选：添加过滤条件，比如按用户ID筛选
  where: (events, { eq }) => eq(events.userId, 'target-user-uuid')
});

这个查询会自动生成高效的SQL，返回的结果结构完全符合你的需求。

2. 方案对比 #

• 单次关联查询：就是上面的Drizzle嵌套查询，只需要一次数据库请求，PostgreSQL会高效处理关联和数据聚合，代码简洁易维护，是最优解。
• 分两次查询合并：先查所有events，再查所有关联的labels，最后在Node.js里手动匹配合并。这种方式需要两次数据库请求，代码冗余，数据量大时性能不如单次查询，不推荐。

注意事项 #

• 确保你的Drizzle ORM是最新版本，旧版本可能对多对多嵌套查询支持有限
• 只选择需要的字段（比如这里只选label的id），可以减少数据传输量，提升查询效率

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