GroupBy DAY using Doctrine2

Question

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

and the error is:

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

And the second way is:

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

and the error is:

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

in the second case try to use the ` character before and after `group` — cheesemacfly, Jul 17 '13 at 19:10

score 3 · Accepted Answer · answered Jul 17 '13 at 19:50

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.

Use the NativeQuery class (http://docs.doctrine-project.org/en/latest/reference/native-sql.html)
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)
Install a bundle that adds the grouping methods for you https://github.com/beberlei/DoctrineExtensions

Thanks for help, I used nativeQuery and it's working very well. — user2576422, Jul 19 '13 at 10:57

score 2 · Answer 2 · answered Feb 24 '16 at 08:14

2

For those who want to use the DQL, there is a way to do it:

$query->select('count(p), p.transactionDate, SUBSTRING(p.transactionDate, 1, 10) as my_date')
    //...
    ->groupBy('my_date');

See the doc for SUBSTRING

SUBSTRING(str,pos,len). The forms with a len argument return a substring len characters long from string str, starting at position pos.

In my example, i start at position 1 to 10 which gives me YYYY-MM-DD. For instance, if you just want the day, you can start at position 9 with a length of 2

SUBSTRING(p.transactionDate, 9, 2)

answered Feb 24 '16 at 08:14

Elyass

726
8
15

Doing this against sql server didn't work (got unknown column), probably because of [this](https://stackoverflow.com/a/3841804/4126114) – Shadi Aug 16 '17 at 14:22
Doing `groupBy("substring(...)")` yields a Doctrine semantic error – Shadi Aug 16 '17 at 14:27
Just filed doctrine2 [issue 6629](https://github.com/doctrine/doctrine2/issues/6629) – Shadi Aug 16 '17 at 14:42

GroupBy DAY using Doctrine2

2 Answers2

Linked