Doctrine: Group by date ranges

Question

In my Symfony app I have an entity 'Project' which contains two fields: 'createdOn' (type = date) and 'individual'. An individual can occur multiple times in 'Project'.

_created_On_|_individual_id
 2012.12.01 |     3
 2012.12.24 |     5
 2013.01.10 |     9

I'm trying to build a query to count all distinct individuals grouped by 'createdOn' in such a way, that I get results sorted by month. And it must be possible to set a date range for the query.

My query so far:

'SELECT p.createdOn, COUNT (DISTINCT p.individual) 
                FROM ...\DossierBundle\Entity\Project p
                WHERE p.createdOn
                BETWEEN :name1
                AND :name2' 
                )->setParameters(array(
                'name1' => $startDate,
                'name2' => $endDate,
                ))

This doesn't quite get me the desired result below

_DATE____|_Number_of_Individuals
Dec 2012 | 2
Jan 2013 | 1

But instead I get

__DATE_____|_Number_of_Individuals
2012.12.01 |     1
2012.12.24 |     1
2013.01.10 |     1 

Google didn't help me either so any support will be much appreciated.

Flo

Answer 1

You need to extend doctrine with custom dates functions , and be carefull because you cant use group by with functions so you'll have to trick doctrine.

have a look at that :

http://www.doctrine-project.org/blog/doctrine2-custom-dql-udfs.html

here is an exemple of a day native mysql function :

https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/Day.php

and read that for the group by issue and work around ( using as ) :

http://www.doctrine-project.org/jira/browse/DDC-1236

i needed to group visits by date ( without the time ) , so i wrote that dql query with a date extension :

select count(v) as visit_count , DATE(v.created_at) as day_created_at from Shorten\Entity\Visit v group by day_created_at 

hope it helps.