Order by count in Doctrine

Viewed 1732

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'

2 Answers
Related