TypeORM是否支持MINUS运算符？如何查询无新同target_id的历史请求

实现方案 #

你要的需求无需使用MINUS运算符，有两种兼容性更好、性能更稳定的实现方式：

方案1：NOT EXISTS 子查询（最推荐） #

逻辑最直观，数据库优化器对这类查询的优化也非常成熟，SQL逻辑如下：

SELECT r1.* 
FROM request r1
WHERE r1.updated_at < :deadline
-- 过滤掉存在近1个月更新记录的target_id
AND NOT EXISTS (
    SELECT 1 FROM request r2 
    WHERE r2.target_id = r1.target_id 
    AND r2.updated_at > :deadline
)

对应TypeORM代码：

// 计算1个月前的时间节点
const deadline = new Date();
deadline.setMonth(deadline.getMonth() - 1);

const result = await connection
  .getRepository(Request)
  .createQueryBuilder('r1')
  .where('r1.updated_at < :deadline', { deadline })
  .andWhere((qb) => {
    const subQuery = qb
      .subQuery()
      .select('1')
      .from(Request, 'r2')
      .where('r2.target_id = r1.target_id')
      .andWhere('r2.updated_at > :deadline', { deadline })
      .getQuery();
    return `NOT EXISTS ${subQuery}`;
  })
  .getMany();

方案2：左连接+空值过滤 #

通过左关联近1个月的请求记录，过滤关联结果为空的行，即可得到没有近1个月更新的target_id对应的旧请求：

SELECT DISTINCT r1.*
FROM request r1
LEFT JOIN request r2 
  ON r1.target_id = r2.target_id 
  AND r2.updated_at > :deadline
WHERE r1.updated_at < :deadline
AND r2.id IS NULL

对应TypeORM代码：

const deadline = new Date();
deadline.setMonth(deadline.getMonth() - 1);

const result = await connection
  .getRepository(Request)
  .createQueryBuilder('r1')
  .leftJoin(
    Request, 
    'r2', 
    'r1.target_id = r2.target_id AND r2.updated_at > :deadline', 
    { deadline }
  )
  .where('r1.updated_at < :deadline', { deadline })
  .andWhere('r2.id IS NULL')
  .distinct(true)
  .getMany();

关于MINUS运算符的补充说明 #

TypeORM的QueryBuilder没有内置MINUS相关方法，如果你确实需要使用该运算符，可以直接拼接原生SQL实现，示例如下：

const deadline = new Date();
deadline.setMonth(deadline.getMonth() - 1);

// 先通过MINUS拿到符合要求的target_id集合
const targetIdRows = await connection.manager.query(
  `SELECT target_id FROM request WHERE updated_at < :deadline
   MINUS
   SELECT target_id FROM request WHERE updated_at > :deadline`,
  { deadline }
);
const targetIds = targetIdRows.map(row => row.target_id);

// 再查询对应target_id的所有旧请求
const result = await connection
  .getRepository(Request)
  .createQueryBuilder('r')
  .where('r.target_id IN (:...targetIds)', { targetIds })
  .andWhere('r.updated_at < :deadline', { deadline })
  .getMany();

该方案依赖数据库对MINUS运算符的支持（MySQL需8.0.31以上版本才支持等效的EXCEPT运算符，Oracle等数据库原生支持），兼容性不如前两种方案，更推荐使用前两种实现。

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