TypeORM: How to add COUNT field when using getMany()

Question

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"

Answer 1

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();
}