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
- 100610385
- 106952005
- 108410557
- ...
- ...
CodePudding user response:
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')
->select('u, count(u) AS HIDDEN uniCount')
->orderBy('uniCount', 'ASC')
->groupBy('u.id')
->setMaxResults(5)
->getQuery()
->getResult()
;
}