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 ?

Ambitious

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

Prather

ERROR: column \"a2_.title\" must appear in the GROUP BY clause or be used in an aggregate function – Ambitious

@user1185460: are you sure you're adding only "a.id" and not whole "a" entity? Because, of course, if you add whole entity, all fields have to be listed into groupBy function. This isn't Symfony2 nor Doctrine2 constraint, is SQL one. – Prather

my bad I overlooked, yes then it works , which is one step in the right direction, but I still don't have the full article , yes if it was pure SQL i would have done it with a subquery , i could always go with a doctrine's native query, but before going that way i would like to know if there's an idiomatic way i'm missing – Ambitious

@user1185460: if you want the full article, you need to add only "a" to select BUT you need to add all fields to "groupBy" method. – Prather

wait it seems the groupBy(a) works , damn it ! i thought it didn't worked before because my sample data were not doing leftJoin :), so if you edit your question to be select('COUNT(u) as HIDDEN nbrLikes', 'a') group by 'a', then it works, and i got the entity :) – Ambitious

@user1185460: it was what I tried to told you here :) BTW I've updated my answer for clearness – Prather

you need the "hidden" if you want the entity, otherwise it's an associative array – Ambitious

Answer 2

You can have a look at 2 functions:

->addSelect

Which (I believe) won't work when creating the queryBuilder, but should be used with the way below.

You can also use the base query builder this way:

    $query = $this->getEntityManager()->createQueryBuilder()
        ->select('article, nbrLikes, ...')
        ->from('BundleName:ClassName', 'article')
        ->leftJoin('article.likedByUsers', 'nbrLikes')

That will always select and populate the entities in the select. You can also do some partial select to avoid getting necessary fields. This definitly isn't a solution but may point you in the right direction.

Tabaret