如何在Drizzle ORM的WHERE子句中使用子查询实现指定查询

数据背景 #

表A的原始数据：

id  name    last_updated
1   Apple   100
2   Banana  100
3   Apple   200
4   Banana  200
5   Carrot  200
6   Banana  300
7   Apple   300

需求 #

获取每个name对应的last_updated小于等于指定参数（示例：250）的最新记录，预期结果：

数据库初始化SQL #

-- INIT database
CREATE TABLE IF NOT EXISTS A (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    last_updated INTEGER NOT NULL
);
INSERT INTO A (name, last_updated) VALUES ('Apple', 100);
INSERT INTO A (name, last_updated) VALUES ('Banana', 100);
INSERT INTO A (name, last_updated) VALUES ('Apple', 200);
INSERT INTO A (name, last_updated) VALUES ('Banana', 200);
INSERT INTO A (name, last_updated) VALUES ('Carrot', 200);
INSERT INTO A (name, last_updated) VALUES ('Banana', 300);
INSERT INTO A (name, last_updated) VALUES ('Apple', 300);

原生SQL实现 #

-- QUERY database
SELECT a.id, a.name, a.last_updated
FROM A AS a
WHERE a.last_updated = (
    SELECT MAX(last_updated)
    FROM A
    WHERE name = a.name
    AND last_updated <= 250
)
AND a.last_updated <= 250;

错误的Drizzle尝试 #

const dayLuxonToBigint = 250

return await db
  .select()
  .from(A)
  .where(
    and(
      lte(a.last_updated, dayLuxonToBigint),
      sql`SELECT max(${A.last_updated}) FROM ${A}
WHERE id = a.id
AND ${A.last_updated} <= ${dayLuxonToBigint}`,
    ),
  );

正确的Drizzle ORM写法 #

写法一：直接对应原生SQL逻辑 #

这种写法完全匹配原生SQL的子查询逻辑，注意给内层表加别名避免字段歧义：

import { sql, eq, lte, and } from 'drizzle-orm';
import { db } from './your-db-connection'; // 替换为你的数据库连接
import { A as a } from './schema'; // 替换为你的schema定义

const dayLuxonToBigint = 250n; // *注意类型匹配，SQLite INTEGER对应BigInt*

const result = await db
  .select({
    id: a.id,
    name: a.name,
    last_updated: a.last_updated,
  })
  .from(a)
  .where(
    and(
      lte(a.last_updated, dayLuxonToBigint),
      eq(
        a.last_updated,
        sql`(SELECT max(${a.last_updated}) FROM ${a} AS inner_a WHERE inner_a.name = ${a.name} AND inner_a.last_updated <= ${dayLuxonToBigint})`
      )
    )
  );

写法二：类型安全的子查询构造器 #

使用Drizzle内置的子查询API，更符合ORM的类型安全特性：

import { eq, lte, max, and, subquery } from 'drizzle-orm';
import { db } from './your-db-connection';
import { A as a, A as innerA } from './schema';

const dayLuxonToBigint = 250n;

// 子查询：按name分组，获取每个name符合条件的最大last_updated
const maxUpdatedSubquery = db
  .select({
    name: innerA.name,
    maxUpdated: max(innerA.last_updated),
  })
  .from(innerA)
  .where(lte(innerA.last_updated, dayLuxonToBigint))
  .groupBy(innerA.name)
  .as('max_updated_sub');

// 关联子查询，匹配主表的对应记录
const result = await db
  .select({
    id: a.id,
    name: a.name,
    last_updated: a.last_updated,
  })
  .from(a)
  .innerJoin(maxUpdatedSubquery, eq(a.name, maxUpdatedSubquery.name))
  .where(eq(a.last_updated, maxUpdatedSubquery.maxUpdated));

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