typeORM 中多个关联表中多条件查询

typeORM 文档是编写的少了很多东西, 在使用 typeORM 开发项目时走了不少坑, 查了不少资料. 以后慢慢记录一些坑吧

    const components = await this.componentRepo.createQueryBuilder('component')
      .leftJoinAndSelect('component.processes', 'processes')
      .leftJoinAndSelect('component.info', 'info')
      .leftJoinAndSelect('component.brand', 'brand')
      .leftJoinAndSelect('component.partNo', 'partNo')
      .leftJoinAndSelect('component.statusDate', 'statusDate')
      .leftJoinAndSelect('info.application', 'application')
      .leftJoinAndSelect('info.customer', 'customer')
      .innerJoinAndSelect('info.project', 'project')
      .leftJoinAndSelect('info.sales', 'sales')
      .where("FIND_IN_SET(:employeeId, project.employeeIds)", { employeeId })
      .andWhere(new Brackets(qb => {
        if (application) {
          qb.where('application.chname LIKE :application', {  application: `%${application}%` })
          .orWhere('application.egname LIKE :application', {  application: `%${application}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (projectCode) {
          qb.where('project.projectCode LIKE :projectCode', {  projectCode: `%${projectCode}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (projectName) {
          qb.where('info.projectName LIKE :projectName', {  projectName: `%${projectName}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (customerName) {
          qb.where('customer.chname LIKE :customerName', {  customerName: `%${customerName}%` })
          .orWhere('customer.egname LIKE :customerName', {  customerName: `%${customerName}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (platform) {
          qb.where('info.platform LIKE :platform', {  platform: `%${platform}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        // 传入 id
        if (pm) {
          qb.where('component.pmId LIKE :pm', {  pm: `%${pm}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (competitorBrand) {
          qb.where('component.competitorBrand LIKE :competitorBrand', {  competitorBrand: `%${competitorBrand}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (competitorPartNo) {
          qb.where('component.competitorPartNo LIKE :competitorPartNo', {  competitorPartNo: `%${competitorPartNo}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (componentCode) {
          qb.where('component.componentCode LIKE :componentCode', {  componentCode: `%${componentCode}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (brand) {
          qb.where('brand.name LIKE :brand', {  brand: `%${brand}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (partNo) {
          qb.where('partNo.name LIKE :partNo', {  partNo: `%${partNo}%` })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (projectPotential) {
          qb.where('info.potential BETWEEN :start AND :end', {  start: projectPotential.start, end: projectPotential.end })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (componentPotential) {
          qb.where('component.potential BETWEEN :start AND :end', {  start: componentPotential.start, end: componentPotential.end })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (status) {
          qb.where('component.status = :status', {  status: status })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (componentAmt) {
          qb.where('component.amt BETWEEN :start AND :end', {  start: componentAmt.start, end: componentAmt.end })
        }
      }))
      .andWhere(new Brackets(qb => {
        if (projectAmt) {
          qb.where('info.amt BETWEEN :start AND :end', {  start: projectAmt.start, end: projectAmt.end })
        }
      }))
      .skip((page - 1) * pageSize)
      .take(pageSize)
      .getManyAndCount()

    return {
      list: components[0].map((el) => new TracksModel(el)),
      page,
      pageSize,
      total: components[1]
    }

上面几个表, 首先是要建立关联关系, 然后使用 join 进主表 component 中, 不然不能查询子表中的内容. 其次, 在添加条件时, 多个 and 关系, 用 .andWhere 联接起来, 然后 new Brackets 创建一个子查询. 最后出来的数据是一个对象包括对象, 数据的格式肯定不是前端要的格式, 可以用一个模型 TracksModel 去转换一下.

TracksModel 类似以下

export class TracksModel {
    componentId: number
    componentCode: string
    projectInfoId: string

    constructor(track) {
        this.componentId = track?.id
        this.projectInfoId = track?.info?.id
        this.componentCode = track?.componentCode
    }
}