Doctrine2 , get entities order by count of a many to many property

Question

I have two entities

• Article
• User

Article has a relation to user named 'likedByUsers'

Now, I would like to get the articles order by number of likes, but:

• I don't want to have a property 'numberOfLikes' because it's too much problem to keep it updated
• I have too many Articles (100k+) to be realistic to the "sort" in PHP side (and the fact we're reaching the limit of doing the sort is the reason why I'm asking this question)
• I can live with not getting the number of likes in the returned values (as the serializer will later hydrate it)

what I currently have is this:

$builder = $this->createQueryBuilder('a');
    ->select('COUNT(u) AS nbrLikes')
    ->leftJoin('a.likedByUsers', 'u') 
    ->orderBy('nbrLikes', 'DESC')
    ->groupBy('a.id')
    ->getQuery()
    ->getResult()
;

this correctly returns the number of likes (with 0 for articles without likes), but it does NOT return the Articles themselves

I've tried adding

->select('a, COUNT(u) AS HIDDEN nbrLikes')

but it fails because a is not part of the GROUP BY

any ideas ?

Answer 1

If you want to select "multiple" values you need to specify them into select method of query builder. Just like reported below

 $builder = $this->createQueryBuilder('a')
        ->select('COUNT(u) AS HIDDEN nbrLikes', 'a.id')
        ->leftJoin('a.likedByUsers', 'u') 
        ->orderBy('nbrLikes', 'DESC')
        ->groupBy('a.id')
        ->getQuery()
        ->getResult();

You have to remember that result will not be an entity but an associative array

---

If you want full entity

 $builder = $this->createQueryBuilder('a')
        ->select('COUNT(u) AS HIDDEN nbrLikes', 'a')
        ->leftJoin('a.likedByUsers', 'u') 
        ->orderBy('nbrLikes', 'DESC')
        ->groupBy('a')
        ->getQuery()
        ->getResult();