Translating SQL query to Doctrine2 DQL

Question

I'm trying to translate this (My)SQL to DQL

SELECT content, created, AVG(rating)
FROM point
GROUP BY DAY(created)
ORDER BY created ASC

And I'm stuck at GROUP BY part, apparently DAY/WEEK/MONTH isn't recognized as valid "function".

[Semantical Error] line 0, col 80 near '(p.created) ORDER': Error: Cannot group by undefined identification variable.

$this->createQueryBuilder('p')
       ->select('p')
       ->groupBy('DAY(p.created)')
       ->orderBy('p.created', 'ASC')

Q: Is it possible to create this kind of query with query builder, or should I use native query?

According to the DQL documentation, you would need to add a user defined function to get this functionality. — Gordon Linoff

Marko Jovanović Marko Jovanović · Accepted Answer · 2012-05-29T10:03:37

It is not possible to use custom DAY/WEEK/MONTH user functions in GROUP BY queries in Doctrine 2.1.?, only SELECT queries are supported (not sure for 2.2.? branch), so I ended up using native query, and everything works fine.

Quick overview of the code:

// creating doctrines result set mapping obj.
$rsm = new Doctrine\ORM\Query\ResultSetMapping();

// mapping results to the message entity
$rsm->addEntityResult('Omglol\AppBundle\Entity\Message', 'm');
$rsm->addFieldResult('m', 'id', 'id');
$rsm->addFieldResult('m', 'content', 'content');
$rsm->addFieldResult('m', 'rating', 'rating');
$rsm->addFieldResult('m', 'created', 'created');

$sql = "SELECT id, content, AVG(rating) as rating, created
        FROM message 
        WHERE domain_id = ? 
        GROUP BY WEEK(created)";

$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $domainId);
$query->getResult();

Translating SQL query to Doctrine2 DQL

2 Answers