I have a scenario where I need to return some nested relational data as well as execute a COUNT on another relationship. As an example, I want to return Blogs with the array of Authors for the blog, and a COUNT of articles the blog contains:
[
{
id: 1,
name: 'Blog 1',
authors: [
{
id: 1,
name: 'Author 1',
},
{
id: 2,
name: 'Author 2',
},
],
articleCount: 5,
},
{
id: 2,
name: 'Blog 2',
authors: [
{
id: 2,
name: 'Author 2',
},
{
id: 3,
name: 'Author 3',
},
],
articleCount: 2,
},
];
I cannot seem to map the articleCount
using getMany()
, and getRaw()
will not nest the authors.
Is there a way to map the articleCount
in the above, or possibly use getRawMany
to nest the authors?
Example Entity:
interface Blog {
id: string;
name: string;
authors: Author[];
articles: Article[];
articleCount?: number; // field is not mapped to db column
}
Query:
const results = await repository.createQueryBuilder('blog')
.select([
'blog.id',
'blog.name',
'author.id',
'author.name',
])
.addSelect('COUNT(article.id)', 'blog_articleCount') // trying to map this to the entity
.leftJoin('blog.authors', 'author')
.leftJoin('blog.articles', 'article')
.groupBy('blog.id')
.addGroupBy('blog.name')
.addGroupBy('author.id')
.addGroupBy('author.name')
.getMany();
SQL:
SELECT "blog"."id" AS "blog_id",
"blog"."name" AS "blog_name",
"author"."id" AS "author_id",
"author"."name" AS "author_name",
COUNT("article"."id") AS "blog_articleCount"
FROM "blogs" "blog"
LEFT JOIN "blog__authors" "blog_author" ON "blog_author"."blogsId" = "blog"."id"
LEFT JOIN "authors" "author" ON "author"."id" = "blog_author"."authorsId"
LEFT JOIN "articles" "article" ON "article"."blog_id" = "blog"."id"
GROUP BY "blog"."id",
"blog"."name",
"author"."id",
"author"."name"
You can use the .loadRelationCountAndMap
query method to count elements from your relation.
Here is an example to count articles for each users using the TypeOrm QueryBuilder:
async findUsers(): Promise<UsersEntity[]> {
return this.createQueryBuilder('user')
.leftJoin('user.articles', 'articles')
.loadRelationCountAndMap('user.articlesCount', 'user.articles')
.getMany();
}
You can also do the same for a specific user:
async findUserById(id): Promise<UsersEntity> {
return this.createQueryBuilder('user')
.where('user.id=:id', { id })
.leftJoin('user.articles', 'articles')
.loadRelationCountAndMap('user.articlesCount', 'user.articles')
.getOne();
}