What is the best way to make a selection with JOIN?

Question

symfony

There is a category entity and a post entity, a relation of one category to many posts.
You need to display categories with id and category name, and count the number of posts in each category. I don't need access to the collection of post objects, I just need to know their number. So that the request is optimal.
Perhaps this is done much easier and I wasted my time picking QueryBuilder.
In general, I would like to get a collection of Partial Objects, the objects will have only three properties (id, name, posts_count).
But the docs say that Partial Objects are bad practice.
It is possible with a bare request, but it would not be desirable.
Maybe there are some other options?

Answer 1

It is elementary solved by a qveri builder:

$qb = $categoryRepository->createQueryBuilder('category')
    ->addSelect('category.id, category.name')
    ->leftJoin('category.posts', 'posts') 
    ->addSelect('COUNT(posts.id) AS posts_count')
    ->groupBy('category');