如何将PostgreSQL CTE转换为KnexJS实现？

需求说明 #

复制前一天匹配指定study_id的数据，仅当当天表中无对应数据时，将数据插入同一张表（candidates）。

原始PostgreSQL SQL #

WITH selected_studies AS (
    SELECT
        *
    FROM
        candidates
    WHERE
        day = (CURRENT_DATE - INTERVAL '1 day')
        AND study_id IN('RECOV')
) INSERT INTO candidates ("day", "study_id", "site_id", "status", "total", "current", "referrer_token", "total_ids", "current_ids")
SELECT
    CURRENT_TIMESTAMP, "study_id", "site_id", "status", "total", "current", "referrer_token", "total_ids", "current_ids"
FROM
    selected_studies
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            candidates
        WHERE
            day = CURRENT_DATE);

用户尝试的无效代码 #

async createBaselineForToday({ studyIds }) {
    const now = new Date();

    const todaysDate = beginningOfDay(now);
    const yesterdaysDate = beginningOfDayBefore(now);

    const whereRow = prepare({
      day: todaysDate,
      studyId: columns.studyId,
      siteId: columns.siteId,
      status: columns.status,
      total: columns.total,
      current: columns.current,
      referrerToken: columns.referrerToken,
      totalIds: columns.totalIds,
      currentIds: columns.currentIds,
    });

    return this.tx
      .with('select_data_before', qb => {
        qb.select('*').from(tableName).whereIn(columns.studyId, studyIds).where(columns.day, yesterdaysDate);
      })
      .insert({ ...whereRow })
      .select({
        day: todaysDate,
        studyId: columns.studyId,
        siteId: columns.siteId,
        status: columns.status,
        total: columns.total,
        current: columns.current,
        referrerToken: columns.referrerToken,
        totalIds: columns.totalIds,
        currentIds: columns.currentIds,
      })
      .from('select_data_before')
      .whereNotExists(function exists(ex) {
        ex.select(1).from(tableName).where(columns.day, todaysDate);
      });
  }

正确的KnexJS实现 #

async createBaselineForToday({ studyIds }) {
  const now = new Date();
  const todaysDate = beginningOfDay(now);
  const yesterdaysDate = beginningOfDayBefore(now);

  return this.tx
    // 定义CTE，筛选前一天指定studyId的数据
    .with('selected_studies', qb => {
      qb.select(
        columns.studyId,
        columns.siteId,
        columns.status,
        columns.total,
        columns.current,
        columns.referrerToken,
        columns.totalIds,
        columns.currentIds
      )
      .from(tableName)
      .whereIn(columns.studyId, studyIds)
      .where(columns.day, yesterdaysDate);
    })
    // 指定要插入的字段列
    .insert([
      columns.day,
      columns.studyId,
      columns.siteId,
      columns.status,
      columns.total,
      columns.current,
      columns.referrerToken,
      columns.totalIds,
      columns.currentIds
    ])
    // 从CTE中选择数据，day字段用CURRENT_TIMESTAMP
    .select([
      this.tx.raw('CURRENT_TIMESTAMP'),
      columns.studyId,
      columns.siteId,
      columns.status,
      columns.total,
      columns.current,
      columns.referrerToken,
      columns.totalIds,
      columns.currentIds
    ])
    .from('selected_studies')
    // 仅当当天无数据时执行插入
    .whereNotExists(function(ex) {
      ex.select(1)
        .from(tableName)
        .where(columns.day, todaysDate);
    });
}

关键修正点 #

• CTE优化：不需要select *，只选择需要插入的字段，避免冗余数据
• insert写法：Knex中insert配合select时，需传入要插入的字段数组，直接映射select结果到插入列
• CURRENT_TIMESTAMP处理：用this.tx.raw('CURRENT_TIMESTAMP')生成数据库原生时间戳，也可替换为this.tx.fn.now()（注意时区差异）
• 移除无效的whereRow：原代码中手动构造的whereRow无意义，插入数据完全来自CTE的查询结果

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