symfony2-主义-如何使用计数和分组依据进行多重选择 [英] symfony2 - Doctrine - How to do a multiple select with a count and group by

问题描述

在Symfony2和Doctrine中，我想执行一个返回计数和分组依据的查询。

In Symfony2 and Doctrine I would like to execute a query that returns a count and a group by.

这就是我尝试过的。这是我要运行的SQL：

Here's what I've tried. This is the SQL I want to run:

SELECT   `terrain_id` , COUNT( * ) 
FROM     `Partie` 
WHERE     1 =1
GROUP BY `terrain_id`

我的实体：

class Partie
{   
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Gp\UserBundle\Entity\User", 
        inversedBy="parties",         cascade={"persist"})
     * @ORM\JoinColumn(nullable=false)
     */
    private $user;

    /**
     * @ORM\ManyToOne(targetEntity="Gp\JeuxBundle\Entity\Terrain")
     */
    private $terrain;

这是我的PartieRepository

public function getTest(\Gp\UserBundle\Entity\User $user){
    return $this->createQueryBuilder('p')
    ->select('count(p), p.terrain')
    ->where('p.user = :user')
    ->setParameter('user', $user)
    ->groupBy('r.terrain')
    ->getQuery()
    ->getResult();
}

这是我得到的错误：

[Semantical Error] line 0, col 19 near 'terrain FROM': Error: 
Invalid PathExpression.   Must be a StateFieldPathExpression.

推荐答案

您可能想使用本地查询

$sql = "SELECT terrain_id as terrain,
                count(*) AS count "
            ."FROM Partie "
            ."GROUP BY terrain_id;";


$rsm = new ResultSetMapping;
$rsm->addScalarResult('terrain', 'terrain');
$rsm->addScalarResult('count', 'count');
$query = $this->_em->createNativeQuery($sql, $rsm);
return $query->getResult();

只需根据需要添加任何具有/ where子句。

Just add in any having / where clauses as needed.

以下是我的结果：

Array
(
    [0] => Array
        (
            [terrain] => 
            [count] => 7
        )

    [1] => Array
        (
            [terrain] => 1
            [count] => 5
        )

    [2] => Array
        (
            [terrain] => 2
            [count] => 1
        )

)

第一个数组中缺少 terrain 是由于 terrain_id 。

The lack of terrain in the first array is due to null terrain_id.

编辑

OP发生了意外的结果，因此在这里以下是一些故障排除步骤：

OP has unexpected results, so here are some troubleshooting steps:

1）尝试 var_dump（$ query-> getSQL（））; 之前使用 return 语句，然后直接对您的数据库运行SQL。如果这产生不正确的结果，请检查查询并适当地更改 $ sql 。

1) Try a var_dump($query->getSQL()); right before the return statement, and run the SQL directly against your DB. If this produces incorrect results, examine the query and alter the $sql as appropriate.

2）如果＃1产生正确的结果，请在之前返回语句之前尝试 var_dump（$ query-> getResult（））; 。如果这能产生正确的结果，则说明您的代码中有更深层的内容。现在该看看为什么要过滤地形了。它可能就像在SQL和 addScalarResult 中删除​​或更改别名一样简单。

2) If #1 produces correct results, try a var_dump($query->getResult()); right before the return statement. If this produces correct results, something is going on deeper in your code. It's time to look at why terrain is being filtered. It may be as simple as removing or changing the alias in SQL and addScalarResult.

3）尝试更简单函数：

3) Try an even simpler function:

    $sql = "SELECT distinct(terrain_id) FROM Partie;";

    $rsm = new ResultSetMapping;
    $rsm->addScalarResult('terrain_id', 'terrain_id');
    $query = $this->_em->createNativeQuery($sql, $rsm);
    var_dump($query->getSQL());
    var_dump($query->getResult());
    return $query->getResult();

这篇关于symfony2-主义-如何使用计数和分组依据进行多重选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！