Sorting the most relation with Doctrine

Question

Sorting the most relation with Doctrine?

Relation

#[ORM\ManyToOne(inversedBy: 'users')]
#[ORM\JoinColumn(nullable: false)]
private ?University $university = null;

My repository code ( Doesn't sort correctly )

public function sortPopularChats(): array
{

    return $this->createQueryBuilder('u')
        ->orderBy('u.university', 'DESC')
        ->groupBy('u.university')
        ->setMaxResults(5)
        ->getQuery()
        ->getResult()
        ;
}

My user table

|id|university_id|

|1|100610385|...
|2|106952005|...
|5|100610385|...
|11|108410557|...
|6|100610385|...
|7|106952005|...
|4|100610385|...
|9|106952005|...
|10|100610385|...

Sorting should be like this

1. 100610385
2. 106952005
3. 108410557
4. ...
5. ...

Answer 1

You need a count expression in order to be able to use it as the ordering field, but doing that it'll return a ScalarResult, with both the count and the object. To prevent that, you can use the HIDDEN keyword (scroll down or search for it) so only the University entities are returned:

public function sortPopularChats(): array
{
    return $this->createQueryBuilder('e')
        ->leftJoin('e.university', 'u')
        ->addSelect('u, count(u) AS HIDDEN uniCount')
        ->orderBy('uniCount', 'ASC')
        ->groupBy('u.id')
        ->setMaxResults(5)
        ->getQuery()
        ->getResult()
        ;
}