Search code examples
countaggregatedql

(Doctrine with Symfony 4) Aggregate functions along with a regular DQL statement?

I have the following DQL statement, using Doctrine's querybuilder:

As you can see I am bringing back all the posts and their comments: 

public function getAllPosts(User $user){

    $qb = $this->createQueryBuilder('p');
    $qb->select('p, postPhotos,postVideos, comments, commentUser, commentUserPhoto, replyComments, commentReplyUser, commentReplyUserPhoto, postTier,creator,creatorPhoto,creatorTiers,creatorSubscriptions')
        ->leftJoin('p.photos', 'postPhotos')
        ->leftJoin('p.videos', 'postVideos')
        ->leftJoin('p.comments', 'comments')
        ->leftJoin('comments.user', 'commentUser')
        ->leftJoin('commentUser.photos', 'commentUserPhoto', 'WITH', 'commentUserPhoto.type = :profileType')
        ->leftJoin('comments.children', 'replyComments')

I have already tried adding a 

->addSelect("COUNT(p.comments) as countComments")

And I just get an error, "countComments' does not point to a Class"

So I looked up other references, like this one: https://symfonycasts.com/screencast/doctrine-queries/select-sum

But it doesn't give an example of how to include a count in with the results of a DQL query.

Do I need to just create my own count function within the comments repository, and loop through my original data set, calling it once per post?

Solution

  • There 2 issues with what you are trying to do:

    1) You should use the join alias "comments", not the relation field "p.comments", in the aggregate function:

    ->addSelect("COUNT(comments) as countComments")

    and the query will be grouped by the root entity, unless ->groupBy() is specified.

    But this is not a solution in your case, the real issue is that:

    2) You cannot select fields of a table when you are trying to aggregate any of its field. It will not work and will produce strange results. More specific, you will get just one entity in the result collection.

    More why cant you mix aggregate values and non aggregate values in a single select

    Solution in your case

    Since you are already selecting the "comments", just "count" the collection resulted:

    $posts = $queryBuilder->getQuery()->getResult();
foreach($posts as $post) {
    echo $post->getComments()->count();
}

    or

    $posts = $queryBuilder->getQuery()->getArrayResult();
foreach($posts as $post) {
    echo count($post['comments']);
}

    References: