不知道你会搜什么会搜到这篇博客。。。因为我也不知道怎么描述
目前user表 learning_communities字段数据是这样的 [{"id": "03b5687b-b016-4dbc-8f61-4fe47706318e"}],取出id来,然后count 一下,排序。 大概就这样。 我用了个子查询
Typeorm_ from 子查询
jsonb 类型统计数量
group by
需求是根据user 表 上learning_communities(jsonb 类型)获取 learning_community 所拥有的用户量(count(learning_community .id)),并排序
最终结果是join learning_community表,获取learning_community表上的name
代码:
async learnCommunityStatistics(pageIndex: number, pageSize: number, sort: 'ASC' | 'DESC') {
let sortBy:'ASC' | 'DESC' = 'DESC';
if (sort) {
sortBy = sort;
}
const community = await this
.createQueryBuilder()
.select('lc."id", lc."name"')
.addSelect('COUNT(c.communityId)', 'count')
.from(((subQuery: any) => {
return subQuery
// tslint:disable-next-line:max-line-length
.select(`cast( jsonb_array_elements ( "user".learning_communities ) ->> 'id' AS uuid ) communityId`)
.from(User, 'user');
}), 'c')
.leftJoin('learning_community', 'lc', 'c.communityId = lc."id"') //这里拼left join on
.groupBy('c.communityId')
.addGroupBy('lc.name, lc."id"')
.orderBy('count', sortBy)
.skip(pageIndex * pageSize)
.take(pageSize)
.getRawMany();
return community;
}
生成的数据库语句
SELECT
lc."id",
lc."name",
COUNT ( C.communityId ) AS "count"
FROM
( SELECT CAST ( jsonb_array_elements ( "user".learning_communities ) ->> 'id' AS uuid ) communityId FROM "user" "user" ) c
LEFT JOIN learning_community lc on c.communityId = lc."id"
GROUP BY c.communityId, lc.name, lc."id"