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.

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

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

Comments

[adrien_om]

If I add the addSelect I get the same error as above and if I add the from I get the following error : [Semantical Error] line 0, col 94 near 'p WHERE user': Error: 'p' is already defined. as it's defined in : $this->createQueryBuilder('p') So I tried to used $this->getEntityManager()->createQuery() instead of $this->createQueryBuilder('p') but I get the following error Error: Call to undefined method Doctrine\ORM\Query::select()

[adrien_om]

I only get the 'count' with this method even if the query looks good. Any idea why ?

[Rob]

@adrien_om I'm not sure. My test code works on my end. Please update your original post with your new code and results.

[adrien_om]

do you have this king of relation too ? ** * @ORM\ManyToOne(targetEntity="Gp\JeuxBundle\Entity\Terrain") */ private $terrain;

[Rob]

@adrien_om Are your results shown from a var_dump right before return $query>getResult()?

[Rob]

My terrain property is not private, it's protected. But I tried both, and that doesn't seem to make a difference, at least not in a Native Query.

[adrien_om]

My results are shown from a var_dump of the return of my function. I didn't mean to talk about private or protected but about ManyToOne

[Rob]

Tried chat, but you don't have enough rep yet. Let's try this one step at a time. I will edit my post with the steps.

[adrien_om]

Yes, I edited my post. I finally get what I wanted thanks to your posts. I'm now trying to get the entity instead of the id. Hope I'll be luckier :)