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,
};
结论
使用原始查询后,分页采用
mysql
中的limit
和offset
,分页总数total
需要根据查询条件查出来。
take
和skip
只支持getManyAndCount
()。
总结
如果你的 mysql 用的比较熟练,对于 sql 语句的查询比较擅长,实际上稍微复杂的查询还是用原始查询比较效率。我不习惯在业务层中循环简单的查询结果去构造复杂的数据,觉得效率很低,而 typeorm 封装的底层查询方法还是值得用的,提供了不少方便。