sql - 将 SQL 查询转换为 Doctrine2 DQL

Question

我正在尝试将此 (My)SQL 转换为 DQL

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

我被困在 GROUP BY 部分，显然 DAY/WEEK/MONTH 未被识别为有效的“函数”。

[语义错误] 第 0 行，“(p.created) ORDER”附近的第 80 列:错误:无法按未定义的标识变量分组。

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

问:是否可以使用查询构建器创建此类查询，还是应该使用 native 查询？

Answer 1

不可能在 Doctrine 2.1 的 GROUP BY 查询中使用自定义的 DAY/WEEK/MONTH 用户函数。？，只支持 SELECT 查询(不确定 2.2.？分支)，所以我最终使用了原生查询，一切正常。

代码快速概览:

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