Node.js中Knex+Objection下，多对多关联表字段过滤实现方法

首先要确保你的模型关系定义正确，必须在多对多关系的through配置中指定中间表的额外字段，否则Objection无法识别并加载这些字段：

// Class模型
class Class extends Model {
  static get tableName() {
    return 'class';
  }

  static get relationMappings() {
    return {
      adventures: {
        relation: Model.ManyToManyRelation,
        modelClass: Adventure,
        join: {
          from: 'class.id',
          through: {
            from: 'class_has_adventure.class_id',
            to: 'class_has_adventure.adventure_id',
            extra: ['date_start', 'date_stop'] // 声明中间表的额外字段
          },
          to: 'adventure.id'
        }
      }
    };
  }
}

// Adventure模型
class Adventure extends Model {
  static get tableName() {
    return 'adventure';
  }

  static get relationMappings() {
    return {
      classes: {
        relation: Model.ManyToManyRelation,
        modelClass: Class,
        join: {
          from: 'adventure.id',
          through: {
            from: 'class_has_adventure.adventure_id',
            to: 'class_has_adventure.class_id',
            extra: ['date_start', 'date_stop']
          },
          to: 'class.id'
        }
      }
    };
  }
}

场景1：查询Class时过滤中间表字段 #

如果要返回存在符合时间条件的关联adventure的Class，并且仅加载符合条件的adventure数据，可以结合withGraphJoined、modifyGraph和whereExists实现：

const currentDate = new Date();

const targetClasses = await Class.query()
  // 关联adventures并join中间表
  .withGraphJoined('adventures')
  // 过滤关联的adventures，只保留时间范围内的项
  .modifyGraph('adventures', builder => {
    builder
      .where('class_has_adventure.date_start', '<=', currentDate)
      .andWhere('class_has_adventure.date_stop', '>=', currentDate);
  })
  // 确保只返回有符合条件关联的Class
  .whereExists(
    Class.relatedQuery('adventures')
      .where('class_has_adventure.date_start', '<=', currentDate)
      .andWhere('class_has_adventure.date_stop', '>=', currentDate)
  )
  // 去重，避免同一Class被重复返回
  .distinct();

如果仅需过滤Class，不关心返回的adventure列表，可以简化为：

const targetClasses = await Class.query()
  .join('class_has_adventure', 'class.id', 'class_has_adventure.class_id')
  .where('class_has_adventure.date_start', '<=', currentDate)
  .andWhere('class_has_adventure.date_stop', '>=', currentDate)
  .distinct();

场景2：查询Adventure时过滤中间表字段 #

逻辑和查询Class一致，只需替换模型和关系名：

const currentDate = new Date();

const targetAdventures = await Adventure.query()
  .withGraphJoined('classes')
  .modifyGraph('classes', builder => {
    builder
      .where('class_has_adventure.date_start', '<=', currentDate)
      .andWhere('class_has_adventure.date_stop', '>=', currentDate);
  })
  .whereExists(
    Adventure.relatedQuery('classes')
      .where('class_has_adventure.date_start', '<=', currentDate)
      .andWhere('class_has_adventure.date_stop', '>=', currentDate)
  )
  .distinct();

使用withGraphFetch的注意事项 #

如果用withGraphFetch（单独查询关联数据），需要手动在关联builder中join中间表才能过滤：

const targetClasses = await Class.query()
  .withGraphFetch('adventures')
  .modifyGraph('adventures', builder => {
    builder
      .join('class_has_adventure', 'adventure.id', 'class_has_adventure.adventure_id')
      .where('class_has_adventure.date_start', '<=', currentDate)
      .andWhere('class_has_adventure.date_stop', '>=', currentDate)
      // 选择中间表的额外字段，否则不会返回
      .select('adventure.*', 'class_has_adventure.date_start', 'class_has_adventure.date_stop');
  })
  .whereExists(
    Class.relatedQuery('adventures')
      .where('class_has_adventure.date_start', '<=', currentDate)
      .andWhere('class_has_adventure.date_stop', '>=', currentDate)
  );

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