如何将带NULL值比较的PSQL查询正确转换为KnexJS连表函数

问题描述 #

需要将指定PSQL查询转换为KnexJS实现，核心难点是内连接时site_id字段需要支持「两边值相等 或 两边均为NULL」的匹配逻辑，现有编写的Knex代码无法正确生成该部分条件，导致查询结果不符合预期。

原始PSQL查询 #

SELECT
    sum("current")
FROM (
    SELECT
        "study_id",
        "site_id",
        "status",
        max("day") AS "day"
    FROM
        "candidates"
    WHERE
        "study_id" in('TBX1')
        AND "status" in('INCOMPLETE')
    GROUP BY
        "study_id",
        "site_id",
        "status") AS "latest"
    INNER JOIN "candidates" ON "latest"."day" = "candidates"."day"
        AND "latest"."study_id" = "candidates"."study_id"
        AND ("latest"."site_id" = "candidates"."site_id" or 
            ("latest"."site_id" is null and "candidates"."site_id" is null))
        AND "latest"."status" = "candidates"."status";

现有存在问题的Knex代码 #

const group = [columns.studyId, columns.siteId, columns.status];
    const filterFn = this.filter;
    const x = this.tx
      .sum(selectColumn)
      .from(function subQuery() {
        this.select(group).max(columns.day, { as: 'day' }).from(tableName).groupBy(group).as('latest');
        filterFn.call({ builder: this }, filter);
      })
      .join(tableName, function joinOn() {
        [columns.day, ...group].map(column =>
          this.on(`latest.${column}`, `${tableName}.${column}`).on(
            this.tx.raw(`("latest"."site_id" = "candidates"."site_id" or
           ("latest"."site_id" is null and "candidates"."site_id" is null))`)
          )
        );
      })

现有代码的问题点：

• 遍历所有关联字段统一生成on等值条件时，会给site_id加上默认的等值判断，SQL中NULL = NULL返回结果为unknown，会直接把两边site_id都为NULL的记录过滤掉
• 遍历过程中重复拼接site_id的NULL判断raw，会生成多余的重复条件
• 硬编码了表名candidates，如果tableName变量变更会直接报错
• 子查询中select(group)没有展开数组，会导致生成的SQL子查询字段缺失
• filterFn绑定的上下文错误，会导致筛选逻辑执行异常

正确实现方案 #

不要遍历所有字段统一生成等值条件，单独给site_id编写特殊匹配逻辑，其余字段正常走等值匹配即可，Knex内置的orOn、onNull方法可以完美实现带括号的NULL值匹配逻辑，不需要手写raw。
修正后的完整代码：

const group = [columns.studyId, columns.siteId, columns.status];
const filterFn = this.filter;
const x = this.tx
  .sum(selectColumn)
  .from(function subQuery() {
    // 展开group数组，确保子查询字段正确
    this.select(...group)
      .max(columns.day, { as: 'day' })
      .from(tableName)
      .groupBy(group)
      .as('latest');
    // 修正上下文绑定，直接传入当前子查询builder
    filterFn.call(this, filter);
  })
  .join(tableName, function joinOn() {
    // 普通字段走正常等值匹配
    this.on('latest.day', `${tableName}.day`);
    this.on(`latest.${columns.studyId}`, `${tableName}.${columns.studyId}`);
    this.on(`latest.${columns.status}`, `${tableName}.${columns.status}`);
    
    // site_id单独处理：等值匹配 或 两边均为NULL，回调嵌套会自动生成括号保证逻辑优先级
    this.on(function() {
      this.on(`latest.${columns.siteId}`, `${tableName}.${columns.siteId}`)
        .orOn(function() {
          this.onNull(`latest.${columns.siteId}`)
            .andOnNull(`${tableName}.${columns.siteId}`);
        });
    });
  })

可选raw写法 #

如果习惯用raw实现，也可以用Knex的raw占位符语法避免硬编码表名：

// 替换上面site_id的处理逻辑即可
this.on(
  this.tx.raw(
    `?? = ?? OR (?? IS NULL AND ?? IS NULL)`,
    [
      `latest.${columns.siteId}`,
      `${tableName}.${columns.siteId}`,
      `latest.${columns.siteId}`,
      `${tableName}.${columns.siteId}`
    ]
  )
);

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