NestJs中使用Typeorm的问题总结


Typeorm 无法在连接查询中使用子查询?

场景

  • 客户资源表CustomerResource,此表为主表
  • 客户资源账号关联表CustomerResourceAccount,此表为关联表,表内有customer_resource_id与主表id关联
  • 送件状态表DeliveryStatus,此表为关联表,表内有customer_resource_id与主表id关联
  • 现在要查出CustomerResource内所有数据,并且将CustomerResourceAccountDeliveryStatus中关联的数据也一并查出
  • 只需要CustomerResourceAccountDeliveryStatus中的部分字段即可。

原始 sql

select t1.*, t2.username as username, t2.create_time as createTime, t3.back_message as backMessage
from customer_resource t1
left join customer_resource_account t2 on t1.id = t2.customer_resource_id
left join delivery_status t3 on t1.id = t3.customer_resource_id

此 sql 中可以看到,我需要主表全部字段,和副表中部分字段。

typeorm 中怎么写呢?

async customerResourceList(queryOption: ObjectType): Promise<any> {
		 return await getConnection().createQueryBuilder(CustomerResourceEntity, 't1')
    .orderBy({ 't1.createTime': 'DESC' })
    .leftJoinAndMapOne('t1.serviceManagerInfo', qb => qb.select('username', 'username').from(CustomerResourceAccountEntity, 't2'), 't2', 't1.id = t2. customer_resource_id')
    .leftJoinAndMapMany('t1.deliveryInfo', qb => qb.select('back_message', 'backMessage').from(DeliveryStatusEntity, 't3'), 't3', 't1.id = t3. customer_resource_id')
    .getRawMany()
  }

上述代码运行结果

leftJoinAndMapOneleftJoinAndMapMany支持第二个参数传入子查询来查询部分字段,可实际结果只能查询到CustomerResource中的所有字段,关联表中字段并未查出。

调查分析

从 typeorm 源码中可以看到这种子查询用法的声明,理论上既然发布了版本,就不该有这么大的 bug,但后来从 github 的 issues 得到了作者确认此 bug 存在。

解决办法

在我的这个业务中,由于两个关联表的字段都不多,所以我选择全部查出,映射到主表中,作为一个集合返回前端。但其实我们也可以用query方法执行自己的 sql,做到部分字段返回。分页自己做。

代码如下

async customerResourceList(queryOption: ObjectType): Promise<any> {
    // eslint-disable-next-line prefer-const
    const { pageSize = 10, pageNumber = 1, createTimeBegin, createTimeEnd, city, channelIds, isSuper } = channelObject(queryOption);
    this.toolsService.checkPage(pageSize, pageNumber);
    const queryConditionList = [];
    if (createTimeBegin) {
      queryConditionList.push('t1.create_time >= :createTimeBegin');
    }
    if (createTimeEnd) {
      queryConditionList.push('t1.create_time <= :createTimeEnd');
    }
    if (city) {
      queryConditionList.push('t1.city = :city');
    }
    if (channelIds && isSuper === 0) {
      queryConditionList.push('t1.channel_id in (:...channelIds)');
    }
    const queryCondition = queryConditionList.join(' AND ');
    const [data, total] = await getConnection().createQueryBuilder(CustomerResourceEntity, 't1')
    .orderBy({ 't1.createTime': 'DESC' })
    .leftJoinAndMapOne('t1.serviceManagerInfo', CustomerResourceAccountEntity, 't2', 't1.id = t2. customer_resource_id')
    .leftJoinAndMapMany('t1.deliveryInfo', DeliveryStatusEntity, 't3', 't1.id = t3. customer_resource_id')
    .andWhere(queryCondition, { createTimeBegin, createTimeEnd, city, channelIds })
    .skip((pageNumber - 1) * pageSize)
    .take(pageSize)
    .getManyAndCount()

    return {
      data,
      total,
      pageNumber,
      pageSize,
    };
  }

无法在原始查询中使用 take,skip 做分页?

场景

  • 账户表Account
  • 客户资源账号关联表CustomerResourceAccount,表内有account_idaccountid关联
  • 现在要查出Account中的id,username,mobile,当天分配数,当日分配数
  • 由于当天分配数和当日分配数查询较为复杂,所以使用原始查询。

原始 sql

SELECT t3.id, t3.username, t3.mobile, t3.totalCustomerCount, IFNULL(t4.todayCustomerCount,0) as todayCustomerCount
from
(select t1.id,t1.username, t1.mobile, COUNT(t2.account_Id) as totalCustomerCount
from account t1
left join customer_resource_account t2 on (t1.id = t2.account_Id)
where t1.mechanism_input_code = "xx-admin" and t1.id <> 2
GROUP BY t1.username, t1.mobile, t1.id, t2.account_Id) t3
left join
(select t1.id, DATE(t2.create_time) time, count(*) todayCustomerCount
from account t1
left join customer_resource_account t2 on (t1.id = t2.account_Id)
where t1.mechanism_input_code = "xx-admin" and t1.id <> 2
GROUP BY t1.id, t1.username, t1.mobile, DATE(t2.create_time)
HAVING time = CURRENT_DATE) t4
on t3.id = t4.id

typeorm 中使用 query 查询原始 sql

async serviceManagerDetailList(queryOption: ObjectType): Promise<any> {
    // eslint-disable-next-line prefer-const
    const { pageSize = 10, pageNumber = 1, accountId, mechanismInputCode } = channelObject(queryOption);
    this.toolsService.checkPage(pageSize, pageNumber);
    const [ total ] = await getManager().query(
      `
      SELECT count(1) as totalCount
      from
      (select t1.id,t1.username, t1.mobile, COUNT(t2.account_Id) as totalCustomerCount
      from account t1
      left join customer_resource_account t2 on (t1.id = t2.account_Id)
      where t1.mechanism_input_code = "${mechanismInputCode}" and t1.id <> ${accountId}
      GROUP BY t1.username, t1.mobile, t1.id, t2.account_Id) t3
      left join
      (select t1.id, DATE(t2.create_time) time, count(*) todayCustomerCount
      from account t1
      left join customer_resource_account t2 on (t1.id = t2.account_Id)
      where t1.mechanism_input_code = "${mechanismInputCode}" and t1.id <> ${accountId}
      GROUP BY t1.id, t1.username, t1.mobile, DATE(t2.create_time)
      HAVING time = CURRENT_DATE) t4
      on t3.id = t4.id
      `
    )
    const data = await getManager().query(
      `
      SELECT t3.id, t3.username, t3.mobile, t3.totalCustomerCount, IFNULL(t4.todayCustomerCount,0) as todayCustomerCount
      from
      (select t1.id,t1.username, t1.mobile, COUNT(t2.account_Id) as totalCustomerCount
      from account t1
      left join customer_resource_account t2 on (t1.id = t2.account_Id)
      where t1.mechanism_input_code = "${mechanismInputCode}" and t1.id <> ${accountId}
      GROUP BY t1.username, t1.mobile, t1.id, t2.account_Id) t3
      left join
      (select t1.id, DATE(t2.create_time) time, count(*) todayCustomerCount
      from account t1
      left join customer_resource_account t2 on (t1.id = t2.account_Id)
      where t1.mechanism_input_code = "${mechanismInputCode}" and t1.id <> ${accountId}
      GROUP BY t1.id, t1.username, t1.mobile, DATE(t2.create_time)
      HAVING time = CURRENT_DATE) t4
      on t3.id = t4.id
      limit ${pageSize}
      offset ${(pageNumber - 1) * pageSize}
      `
    )

    return {
      data,
      total:Number(total.totalCount),
      pageNumber,
      pageSize,
    };

结论

使用原始查询后,分页采用mysql中的limitoffset,分页总数total需要根据查询条件查出来。

takeskip只支持getManyAndCount()。

总结

如果你的 mysql 用的比较熟练,对于 sql 语句的查询比较擅长,实际上稍微复杂的查询还是用原始查询比较效率。我不习惯在业务层中循环简单的查询结果去构造复杂的数据,觉得效率很低,而 typeorm 封装的底层查询方法还是值得用的,提供了不少方便。


文章作者: Porschebz
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Porschebz !
  目录