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'

score 4 · Accepted Answer · edited Aug 08 '17 at 12:54

4

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

edited Aug 08 '17 at 12:54

famas23

2,072
4
17
53

answered Aug 08 '17 at 12:20

Alessandro Minoccheri

35,521
22
122
171

your solution will return only one array contains the first training, so i just added : ->groupBy('subscription.id_training') to return the list of trainings with "mycount" . But the problem when "mycount" == 0 , the training it's not considred, inner join will not consider it .. so the query result will not contain trainings how does not contain subscriptions – famas23 Aug 08 '17 at 12:46
It works with inner join,some explanations about the diff between innerJoin , leftJoin and join plz? – famas23 Aug 08 '17 at 12:52
1

Here there is a big answer to your question, https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join if this answer has helped you accept It to help other person with your same problem please @Bhs – Alessandro Minoccheri Aug 08 '17 at 12:54
Glad to help you! @Bhs – Alessandro Minoccheri Aug 08 '17 at 12:56
You can also use `SELECT COUNT(subscription.id) as HIDDEN myacount` if you don't need the count in the result. You will still be able to sort by it. – Matěj Koubík Jan 29 '21 at 12:28

Order by count in Doctrine

Trainings Entity:

Subscriptions Entity:

QueryBuilder:

1 Answers1