Sequelize+NestJS：如何按嵌套对象条件查询并保留无关联父数据？

模型定义 #

Mission 模型 #

export class Mission extends Model<Mission> {
...
@HasMany(() => RequestEntity)
  requests: RequestEntity[];
...
}

Request 模型 #

export class Request extends Model<Request>{
...
  @Column({
    type: DataType.STRING,
  })
  status: string;

  @ForeignKey(() => Mission)
  @Column(DataType.UUID)
  missionId: string;

  @ForeignKey(() => User)
  @Column(DataType.UUID)
  userId: string;
...
}

Mission与Request为一对多关联（一个Mission可对应多个Request），查询需求如下：

• 无关联Request的Mission需要返回
• 存在指定userId且状态为pending的Request的Mission需要返回

当前尝试的问题 #

使用以下查询代码：

const query: any = {
    limit: payload.limit,
    offset: payload.offset,
    order: [[payload.column, payload.direction]],
    where: {
      status: "open"
    },
    include: [{
      model: RequestEntity,
      where: { userId: user.id, status: 'pending' },
      required: false,
    }],
    transaction,
  };

• 当required: false时，所有符合主表where条件的Mission都会返回，哪怕它的Request既不匹配userId也不是pending状态，只是不携带requests字段，不符合需求
• 当required: true时，只会返回有匹配Request的Mission，无关联Request的Mission被过滤，不符合需求

解决方案 #

需要将关联条件移到主查询的where中，结合Op.or与子查询实现精准过滤：

import { Op } from 'sequelize';

const query: any = {
  limit: payload.limit,
  offset: payload.offset,
  order: [[payload.column, payload.direction]],
  where: {
    status: "open",
    [Op.or]: [
      // 条件1：当前Mission无关联Request记录
      {
        [Op.notExists]: RequestEntity.findAll({
          attributes: [],
          where: {
            missionId: Mission.primaryKeyAttribute,
          },
        }),
      },
      // 条件2：当前Mission存在符合要求的Request记录
      {
        [Op.exists]: RequestEntity.findAll({
          attributes: [],
          where: {
            missionId: Mission.primaryKeyAttribute,
            userId: user.id,
            status: 'pending',
          },
        }),
      },
    ],
  },
  // 如需返回匹配的requests，可保留include（主查询已过滤，此处仅返回符合条件的关联数据）
  include: [{
    model: RequestEntity,
    where: { userId: user.id, status: 'pending' },
    required: false,
  }],
  transaction,
};

逻辑说明 #

1. 通过Op.or组合两个核心过滤条件，精准筛选出符合需求的Mission
2. Op.notExists用于匹配无任何关联Request的Mission，Op.exists用于匹配存在指定条件Request的Mission
3. 主查询过滤完成后，include仅会为符合条件的Mission携带匹配的requests字段，无关联的则不显示该字段

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