TypeORM查询如何返回包含已软删除关联关系的bsService数据

问题原因 #

你在find方法根配置中传入的withDeleted: true仅对主表bsService生效，TypeORM默认所有关联表的查询仍会自动过滤已软删除的记录，所以关联的分类、活动等数据被软删后，会导致关联查询匹配不到对应记录，最终服务数据无法正常返回。

解决方案 #

方案1：对象格式关联配置（适用TypeORM 0.3+版本） #

将原字符串数组格式的relations改为对象格式，逐层给所有需要忽略软删状态的关联单独添加withDeleted: true配置即可，修改后的代码如下：

findAll = async (
    where?: WhereConditions,
    transactionEntityManager: EntityManager = getManager(),
    order?: 'ASC' | 'DESC',
    withDeleted?: boolean,
  ): Promise<BSService[]> => transactionEntityManager.find(BSService, {
    withDeleted,
    where,
    relations: {
      sla: true,
      activity: {
        category: true
      },
      department: {
        company: true
      },
      attendance: true,
      logs: {
        user: true
      },
      requestingAgent: {
        jobs: true
      },
      alocatedAgent: {
        jobs: true
      },
      category: true
    },
    relationLoadStrategy: 'join',
    order: {
      updateAt: order,
    },
    // 为所有关联配置忽略软删规则
    relationOptions: {
      sla: { withDeleted },
      activity: { 
        withDeleted,
        relationOptions: {
          category: { withDeleted }
        }
      },
      department: {
        withDeleted,
        relationOptions: {
          company: { withDeleted }
        }
      },
      attendance: { withDeleted },
      logs: {
        withDeleted,
        relationOptions: {
          user: { withDeleted }
        }
      },
      requestingAgent: {
        withDeleted,
        relationOptions: {
          jobs: { withDeleted }
        }
      },
      alocatedAgent: {
        withDeleted,
        relationOptions: {
          jobs: { withDeleted }
        }
      },
      category: { withDeleted }
    }
  });

方案2：QueryBuilder实现（兼容所有TypeORM版本） #

如果你的TypeORM版本较低不支持上述对象配置，可以改用QueryBuilder手动关联所有表，给每个关联加上withDeleted()方法：

findAll = async (
    where?: WhereConditions,
    transactionEntityManager: EntityManager = getManager(),
    order?: 'ASC' | 'DESC',
    withDeleted?: boolean,
  ): Promise<BSService[]> => {
    const queryBuilder = transactionEntityManager.createQueryBuilder(BSService, 'bsService')
      .withDeleted(withDeleted)
      .leftJoinAndSelect('bsService.sla', 'sla', undefined, { withDeleted })
      .leftJoinAndSelect('bsService.activity', 'activity', undefined, { withDeleted })
      .leftJoinAndSelect('activity.category', 'activityCategory', undefined, { withDeleted })
      .leftJoinAndSelect('bsService.department', 'department', undefined, { withDeleted })
      .leftJoinAndSelect('department.company', 'company', undefined, { withDeleted })
      .leftJoinAndSelect('bsService.attendance', 'attendance', undefined, { withDeleted })
      .leftJoinAndSelect('bsService.logs', 'logs', undefined, { withDeleted })
      .leftJoinAndSelect('logs.user', 'logUser', undefined, { withDeleted })
      .leftJoinAndSelect('bsService.requestingAgent', 'requestingAgent', undefined, { withDeleted })
      .leftJoinAndSelect('requestingAgent.jobs', 'requesterJobs', undefined, { withDeleted })
      .leftJoinAndSelect('bsService.alocatedAgent', 'alocatedAgent', undefined, { withDeleted })
      .leftJoinAndSelect('alocatedAgent.jobs', 'agentJobs', undefined, { withDeleted })
      .leftJoinAndSelect('bsService.category', 'category', undefined, { withDeleted })
      .where(where)
      .orderBy('bsService.updateAt', order);
    return queryBuilder.getMany();
}

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