symfony - Doctrine 中按计数排序

Question

我有两个通过 OneToMany 双向关系相关的类。对于每个订阅，订阅表中都会添加一个新行。我想获取按最大订阅数量排序的培训列表。这是我的实体:

培训实体:

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

    // ...
}

订阅实体:

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

查询生成器:

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

我遇到了这个异常:

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

Answer 1

您需要添加一个包含计数值的字段，并在 order by It 之后添加

试试这个:

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