NestJs中使用Typeorm的问题总结

NodeJs

发布日期: 2021-03-23

更新日期: 2021-03-23

文章字数: 1.2k

阅读时长: 6 分

阅读次数:

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

场景

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

原始 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()
  }

上述代码运行结果

leftJoinAndMapOne和leftJoinAndMapMany支持第二个参数传入子查询来查询部分字段，可实际结果只能查询到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_id与account表id关联
现在要查出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,
    };