symfony2 - Doctrine - How to do a multiple select with a count and group by

Question

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

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`

With my entity:

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;

This is my 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();
}

This is the error I get:

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

Answer 1

You'll probably want to go with a Native Query

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

Just add in any having / where clauses as needed.

The following is my result:

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

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

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

)

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

EDIT

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

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

Answer 2

This error occurs on this line : select('count(p), p.terrain') where you are trying to use p alias that doesn't exist anymore. The select method override the default alias of the createQueryBuilder(). To avoid this, use addSelect instead or specify clearly the from method. Try this :

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

}

or this :

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