Order by count in Doctrine

Question

I have two classes related via a OneToMany bidirectional relationship. For every subscription a new line will be added to Subscriptions table. I want to get the training list ordered by max subscription number. Here are my entities:

Trainings Entity:

class Trainings
{
    /**
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\Subscriptions", mappedBy="id_training")
     */
    private $subscriptions_lists;

    // ...
}

Subscriptions Entity:

class Subscriptions
{
    /**
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Trainings" , inversedBy="subscriptions_lists")
     * @ORM\JoinColumn(name="id_training", referencedColumnName="id",onDelete="CASCADE")
     */
    private $id_training;

QueryBuilder:

$trainings = $em->getRepository('AppBundle:Trainings')
    ->createQueryBuilder('t')
    ->innerJoin('t.subscriptions_lists', 'subscription')
    ->orderBy('COUNT(subscription.id)', 'DESC')
    ->getQuery()
    ->getResult();

I'm getting this exception:

[Syntax Error] line 0, col 87: Error: Expected known function, got 'COUNT'

Answer 1

You need to add a field containing the count value and after order by It

try this:

$trainings = $em->getRepository('AppBundle:Trainings')
    ->createQueryBuilder('t');

$trainings->select('t, COUNT(subscription.id) AS mycount')
    ->leftJoin('t.subscriptions_lists', 'subscription')
    ->groupBy('subscription.id_training')
    ->orderBy('mycount', 'DESC')
    ->getQuery()
    ->getResult();

Answer 2

you have to select count with an alias, in hidden then you can use it in the order by