Counting Order in Doctrine

Question

Counting Order in Doctrine

I have two classes related to OneToMany bidirectional relationship . For each subscription, a new row will be added to the Subscriptions table . I would like to receive a list of workouts ordered by the maximum subscription number. Here are my entities:

Training facility:

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

    // ...
}

Subscription objects:

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 am getting this exception:

[Syntax error] line 0, col 87: Error: expected known function obtained by 'COUNT'

Any help would be appreciated.

+3

symfony doctrine doctrine2

ahmedbhs 08 Aug 17 at 12:17

source to share

2 answers

you need to select count with an alias, in hidden, you can use it in order

0

tny 08 Aug 17 at 12:19

source to share

Alessandro minoccheri · Accepted Answer · 2017-08-08T12:20:38+0000

You need to add a field containing the invoice value and after ordering 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();

Counting Order in Doctrine

Training facility:

Subscription objects:

QueryBuilder:

More articles: