教义 2 中如何按计数排序？

发布于 2024-11-06 20:11:26 字数 671 浏览 4 评论 0原文

我正在尝试按字段（年份）对我的实体进行分组并对其进行计数。

代码：

public function countYear()
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('b.year, COUNT(b.id)')
        ->from('\My\Entity\Album', 'b')
        ->where('b.year IS NOT NULL')
        ->addOrderBy('sclr1', 'DESC')
        ->addGroupBy('b.year');
    $query = $qb->getQuery();
    die($query->getSQL());
    $result = $query->execute();
    //die(print_r($result));
    return $result;
}

我似乎不能说 COUNT(b.id) AS count 因为它给出了错误，并且我不知道使用什么作为 addOrderby(???, 'DESC') 值？

原文

I'm trying to group my entity by a field (year) and do a count of it.

Code:

public function countYear()
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('b.year, COUNT(b.id)')
        ->from('\My\Entity\Album', 'b')
        ->where('b.year IS NOT NULL')
        ->addOrderBy('sclr1', 'DESC')
        ->addGroupBy('b.year');
    $query = $qb->getQuery();
    die($query->getSQL());
    $result = $query->execute();
    //die(print_r($result));
    return $result;
}

I can't seem to say COUNT(b.id) AS count as it gives an error, and
I do not know what to use as the addOrderby(???, 'DESC') value?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

捂风挽笑 2024-11-13 20:11:26

从 v2.3.0 或更低版本开始，实现 order by 表达式需要许多错误和解决方法：

order by 子句不支持表达式，但您可以将带有表达式的字段添加到 select 并按它排序。因此值得重复的是，Tjorriemorrie 自己的解决方案确实有效：
```
$qb->select('b.year, COUNT(b.id) AS mycount')
   ->from('\My\Entity\Album', 'b')
   ->where('b.year IS NOT NULL')
   ->orderBy('mycount', 'DESC')
   ->groupBy('b.year');
```

主义在平等（例如 =、LIKE、IS NULL）中受到阻碍。选择表达式。对于这些情况，我找到的唯一解决方案是使用子选择或自连接：

$qb->select('b, (SELECT count(t.id) FROM \My\Entity\Album AS t '.
                '其中 t.id=b.id AND b.title LIKE :search) AS isTitleMatch')
   ->from('\My\Entity\Album', 'b')
   ->where('b.title LIKE :search')
   ->andWhere('b.description LIKE :search')
   ->orderBy('isTitleMatch', 'DESC');

要从结果中隐藏附加字段，您可以将其声明为AS HIDDEN。这样您就可以按顺序使用它，而无需将其包含在结果中。

$qb->select('b.year, COUNT(b.id) AS HIDDEN mycount')
   ->from('\My\Entity\Album', 'b')
   ->where('b.year IS NOT NULL')
   ->orderBy('mycount', 'DESC')
   ->groupBy('b.year');

There are many bugs and workarounds required to achieve order by expressions as of v2.3.0 or below:

The order by clause does not support expressions, but you can add a field with the expression to the select and order by it. So it's worth repeating that Tjorriemorrie's own solution actually works:
```
$qb->select('b.year, COUNT(b.id) AS mycount')
   ->from('\My\Entity\Album', 'b')
   ->where('b.year IS NOT NULL')
   ->orderBy('mycount', 'DESC')
   ->groupBy('b.year');
```

Doctrine chokes on equality (e.g. =, LIKE, IS NULL) in the select expression. For those cases the only solution I have found is to use a subselect or self-join:

$qb->select('b, (SELECT count(t.id) FROM \My\Entity\Album AS t '.
                'WHERE t.id=b.id AND b.title LIKE :search) AS isTitleMatch')
   ->from('\My\Entity\Album', 'b')
   ->where('b.title LIKE :search')
   ->andWhere('b.description LIKE :search')
   ->orderBy('isTitleMatch', 'DESC');

To suppress the additional field from the result, you can declare it AS HIDDEN. This way you can use it in the order by without having it in the result.

$qb->select('b.year, COUNT(b.id) AS HIDDEN mycount')
   ->from('\My\Entity\Album', 'b')
   ->where('b.year IS NOT NULL')
   ->orderBy('mycount', 'DESC')
   ->groupBy('b.year');

回复收藏 0 原文

深爱不及久伴 2024-11-13 20:11:26

使用 COUNT(b.id) AS count 时出现什么错误？这可能是因为 count 是保留字。尝试 COUNT(b.id) AS idCount 或类似的。

或者，尝试 $qb->addOrderby('COUNT(b.id)', 'DESC');。

你的数据库系统是什么（mysql，postgresql，...）？

回复收藏 0 原文

森罗 2024-11-13 20:11:26

如果您希望存储库方法返回实体，则不能使用 ->select()，但可以使用 ->addSelect() 进行隐藏选择。

$qb = $this->createQueryBuilder('q')
->addSelect('COUNT(q.id) AS HIDDEN 计数器')
->orderBy('计数器');
$result = $qb->getQuery()->getResult();

$result 将是一个实体类对象。

回复收藏 0 原文

醉南桥 2024-11-13 20:11:26

请尝试此代码 ci 2 + 学说 2

$where = " ";

$order_by = " ";
$row = $this->doctrine->em->createQuery("select a from company_group\models\Post a " 
            .$where." ".$order_by."")
            ->setMaxResults($data['limit'])
            ->setFirstResult($data['offset'])
            ->getResult();`

Please try this code for ci 2 + doctrine 2

$where = " ";

$order_by = " ";
$row = $this->doctrine->em->createQuery("select a from company_group\models\Post a " 
            .$where." ".$order_by."")
            ->setMaxResults($data['limit'])
            ->setFirstResult($data['offset'])
            ->getResult();`

回复收藏 0 原文

~没有更多了~