Group by using month, year in doctrine2

doctrine dql
afsar picture afsar · Sep 17, 2011 · Viewed 10.1k times · Source

How to write a following SQL query in doctrine2 as DQL .

SELECT COUNT(id)  
FROM stats  
WHERE YEAR(record_date) = 2009  
GROUP BY YEAR(record_date), MONTH(record_date)

i.e i would like to group by results based on month,year of datetime field stored in MySQL table.

Answer

repincln picture repincln · Oct 30, 2012

In DQL you could also group by month, year, day etc with SUBSTRING.

For example - group by month (datetime format Y-m-d H:i:s):

SELECT p, SUBSTRING(p.date, 6, 2) as month
FROM Entity p
GROUP BY month

Related questions

How to specify null value as filter in a Doctrine query?

I am using Doctrine 1.1 in Zend. I am trying to write a query that will return records that have a null value in a certain column. $q = Doctrine_Query::create() ->select('a.*') ->from('RuleSet a') …

Read More
Symfony2 Doctrine querybuilder where IN

I losted trilion hours google this but none of the solutions were good. I have this querybuilder: $qb2=$this->createQueryBuilder('s') ->addSelect('u') ->innerJoin('s.user','u') ->where("u.id IN(:followeeIds)") -&…

Read More
Using JOIN in Symfony2/Doctrine SQL

I have a problem while trying to USE QueryBuilder OR DQL. I have the following relation: User <-1:n-> Profile <-n:m-> RouteGroup <-1:n-> Route I would like to make a DQL that …

Read More