GroupBy DAY使用Doctrine2 [英] GroupBy DAY using Doctrine2

问题描述

我正在寻找一种按天分组我的政策的方式。我正在尝试很多例子，如何做到这一点，但仍然有一些错误。谁能帮我这个？
这里我将只显示两个例子，其他我试图与之相似。差异仅在使用SQL的函数ex（CAST，SUBSTRING，DATE ...）
我尝试的第一种方式是：

I am looking for way to group my policies by day. I was trying a lot of examples how to do this but still there are some errors. Can anyone help me with this? Here I will show only two examples, others i was trying were similar to those. Differences were only in used SQL's functions ex(CAST, SUBSTRING, DATE...) First way i was trying is:

$query =  $this->getEntityManager()
                        ->createQueryBuilder();   
$query->select('count(p), p.transactionDate')
                        ->from('GLPolicyBundle:Policy', 'p')
                        ->andwhere('p.shop IN (:shop_id)')
                        ->setParameter('shop_id', $shop_list)
                        ->andWhere($query->expr()->between('p.transactionDate', ':date_from', ':date_to'))
                        ->setParameter('date_from', $date_from, \Doctrine\DBAL\Types\Type::DATETIME)
                        ->setParameter('date_to', $date_to, \Doctrine\DBAL\Types\Type::DATETIME)
                        ->addGroupBy('DAY(p.transactionDate)');

getDQL（）返回：

getDQL() returns:

SELECT count(p), p.transactionDate FROM GLPolicyBundle:Policy p 
WHERE p.shop IN (:shop_id) AND (p.transactionDate BETWEEN :date_from AND :date_to) 
GROUP BY DAY(p.transactionDate)

，错误是：

[语义错误]行0，col 156附近'DAY（p.transa'：错误：不能
group by undefined identification or result variable。 p>

[Semantical Error] line 0, col 156 near 'DAY(p.transa': Error: Cannot group by undefined identification or result variable.

第二种方法是：

$query = $this->getEntityManager()
            ->createQuery("SELECT p,  (p.transactionDate) AS group
                            FROM GLPolicyBundle:Policy p
                            WHERE p.shop IN (:shop_id) AND (p.transactionDate BETWEEN :date_from AND :date_to) 
                            GROUP BY DAY(group)")
                            ->setParameter('shop_id', $shop_list)
                            ->setParameter('date_from', $date_from, \Doctrine\DBAL\Types\Type::DATETIME)
                            ->setParameter('date_to', $date_to, \Doctrine\DBAL\Types\Type::DATETIME);

getDQL（）返回：

getDQL() returns:

SELECT p, (p.transactionDate) AS group FROM GLPolicyBundle:Policy p 
WHERE p.shop IN (:shop_id) AND (p.transactionDate BETWEEN :date_from AND :date_to) 
GROUP BY DAY(group)

，错误是：

[语义错误]行0，col 72附近'FROM GLPolicyBundle：Policy'：
错误：类'FROM'未定义。

[Semantical Error] line 0, col 72 near 'FROM GLPolicyBundle:Policy': Error: Class 'FROM' is not defined.

推荐答案

Doctrine不支持许多本机db函数，因为它应该与许多不同类型数据库。所以你有三个选择。

Doctrine doesn't support many of the native db functions because it is supposed to work with many different types of databases. So you're left with three options.

1. 使用 NativeQuery 类（ http://docs.doctrine-project.org/en/latest/reference/native-sql.html ）


2. 实现自定义SQL walker。以下是按天分组的示例（ https： //github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/Day.php ）


3. 安装一个捆绑包，添加分组方法您 https://github.com/beberlei/DoctrineExtensions

1. Use the NativeQuery class (http://docs.doctrine-project.org/en/latest/reference/native-sql.html)
2. Implement a custom SQL walker. Here is an example for grouping by day (https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/Day.php)
3. Install a bundle that adds the grouping methods for you https://github.com/beberlei/DoctrineExtensions

这篇关于GroupBy DAY使用Doctrine2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！