Knex多表联查：按登录用户身份匹配规则过滤获取KPI数据

需求背景 #

已完成table1（KPI主表）、table2（KPI评分表）的基础关联查询能力，现追加关联table3（KPI贡献者关联表），实现当登录用户ID未匹配KPI主表关联字段时，自动匹配贡献者表的关联条目并返回对应KPI数据。

已有基础查询能力 #

• 从table1查询全量有效（deleted_at = null）的id、name、employee_id字段
• 关联table2的有效（deleted_at = null）数据，匹配规则为table2.kpi_id = table1.id
• 针对table2存在同一kpi_id重复记录的场景，固定取每个kpi_id下id最大的最新评分条目

新增身份匹配规则 #

• 规则1：当登录用户ID（this._loggedInUser.id）匹配table1的employee_id字段时，返回该用户作为直属负责人名下的所有关联有效KPI信息
• 规则2：当登录用户ID无法匹配任何table1的employee_id时，校验用户ID是否匹配table3的contributor_id字段，若匹配则返回对应kpi_id关联的、来自table1、table3的全量有效KPI信息

示例表结构与测试数据 #

1. table1（KPI基础信息表）：字段包含id、name、employee_id、deleted_at，测试数据：
   • id=1：对应kpi1、employee_id=5、deleted_at=null
   • id=2：对应kpi2、employee_id=5、deleted_at=null
   • id=3：对应kpi3、employee_id=9、deleted_at=null
2. table2（KPI评分表）：字段包含id、kpi_id、score、deleted_at，测试数据：
   • id=1：对应kpi_id=1、score=10、deleted_at=null
   • id=2：对应kpi_id=2、score=20、deleted_at=null
   • id=3：对应kpi_id=1、score=30、deleted_at=null
3. table3（KPI贡献者关联表）：字段包含id、kpi_id、contributor_id、deleted_at，测试数据：
   • id=1：对应kpi_id=1、contributor_id=7、deleted_at=null
   • id=2：对应kpi_id=1、contributor_id=9、deleted_at=null

现有代码的问题定位 #

以登录用户ID=7的测试场景为例，table3中存在kpi_id=1、contributor_id=7的有效条目，但查询无返回结果，具体问题点：

• 对table3的关联逻辑错误。代码照搬了table2的窗口函数逻辑，对table3按kpi_id分区、按id倒序仅保留每个kpi_id下id最大的贡献者条目。示例中kpi_id=1下id最大的贡献者条目关联的是用户9，直接将用户7对应的有效贡献记录过滤，无法命中贡献者维度的匹配规则
• 条件分支判断完全失效。代码中searchClause.condition1是固定存在的对象属性，判断永远为真，只会执行第一个查询分支，根本不会走到贡献者匹配的判断逻辑
• 存在语法笔误。关联table2的子查询关联条件中，错误使用了未定义的别名k.id，实际关联的主表别名为t1；table3的关联子查询中错误引用了不存在的表名contributors，与实际表名table3不匹配

预期输出效果 #

登录用户ID=7时，需返回kpi_id=1对应的完整关联条目，包含字段：t1.id=1、t2.id=3、kpi_id=1、score=30、employee_id=5、contributor_id=7。

修复后的可运行代码 #

const loginUserId = this._loggedInUser.id;
const query = knex.select([
    't1.id as id',
    't1.name as name',
    't1.employee_id as employeeId',
    't2.kpi_id as kpiId',
    't2.score as userInputScore',
    't2.id as kpiScoreId',
    't3.contributor_id as contributorId'
])
.from('table1 as t1')
// 关联每个kpi_id下id最大的最新有效评分，修正原笔误
.joinRaw(`left join (
    select t2.*, 
           row_number() over (partition by t2.kpi_id order by t2.id desc) as seqnum 
    from table2 t2
    where t2.deleted_at is null
) t2 on t2.kpi_id = t1.id and t2.seqnum = 1`)
// 关联有效贡献者条目，取消原错误的窗口函数分区过滤逻辑，修正错误表名
.leftJoin('table3 as t3', function() {
    this.on('t3.kpi_id', '=', 't1.id')
        .andOn('t3.deleted_at', '=', knex.raw('null'))
})
// 统一过滤KPI主表的有效数据
.where('t1.deleted_at', null)
// 用OR组合覆盖两种身份匹配规则，替代原失效的if分支
.where((qb) => {
    qb.where('t1.employee_id', loginUserId)
      .orWhere('t3.contributor_id', loginUserId)
})
.orderBy('t1.id');

return await query;

内容的提问来源于stack exchange，提问作者Raiyan Rashid Prodhan