如何用Drizzle ORM复现获取每个事件最新更新的LEFT JOIN LATERAL查询

问题场景 #

需要将以下原生PostgreSQL查询转换为纯Drizzle ORM查询构建器代码，避免直接使用原生SQL：

原SQL查询：

await this.drizzleDB.execute(
  sql`
    SELECT i.id, i.title, iu.description, iu.last_updated_timestamp
    FROM incidents i
    LEFT JOIN LATERAL (
      SELECT *
      FROM incident_updates iu
      WHERE iu.incident_id = i.id
      ORDER BY iu.last_updated_timestamp DESC
      LIMIT 1
    ) iu ON TRUE
    ORDER BY i.id
  `
);

对应的Drizzle表定义：

export const incidents = pgTable('incidents', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
});

export const incidentUpdates = pgTable('incident_updates', {
  id: serial('id').primaryKey(),
  incidentId: integer('incident_id').references(() => incidents.id),
  description: text('description').notNull(),
  lastUpdatedTimestamp: timestamp('last_updated_timestamp').defaultNow(),
  status: text('status').notNull(),
});

实现方案 #

Drizzle ORM支持通过子查询结合leftJoin实现LEFT JOIN LATERAL逻辑，以下是纯查询构建器的实现代码：

import { desc, sql } from 'drizzle-orm';

// 定义获取单条最新事件更新的子查询
const latestUpdateSubquery = db
  .select({
    incidentId: incidentUpdates.incidentId,
    description: incidentUpdates.description,
    lastUpdatedTimestamp: incidentUpdates.lastUpdatedTimestamp,
  })
  .from(incidentUpdates)
  .where(sql`${incidentUpdates.incidentId} = ${incidents.id}`)
  .orderBy(desc(incidentUpdates.lastUpdatedTimestamp))
  .limit(1);

// 主查询关联子查询，复现LEFT JOIN LATERAL逻辑
const result = await db
  .select({
    id: incidents.id,
    title: incidents.title,
    description: latestUpdateSubquery.description,
    lastUpdatedTimestamp: latestUpdateSubquery.lastUpdatedTimestamp,
  })
  .from(incidents)
  .leftJoin(latestUpdateSubquery, sql`true`)
  .orderBy(incidents.id);

代码说明 #

• 子查询逻辑：子查询筛选出与当前主表incidents记录关联的更新，按lastUpdatedTimestamp倒序排序后取最新一条，通过sql模板字符串建立与主表的关联条件。
• 主查询关联：使用leftJoin关联子查询，sqltrue对应原SQL中的ON TRUE`，确保所有主表记录都被保留，即使没有对应更新。
• 字段映射：明确指定查询返回的字段，保证类型安全，同时与原SQL的返回结构完全一致。

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