Group By Date Query

Yii 1.1 General Discussion
1

Hey, I’m trying to run the following query on a model to build a custom menu, but don’t seem to be getting the right results? Any ideas?


SELECT MONTH(published_on) as m, YEAR(published_on) as y

FROM pressrelease

GROUP BY YEAR(published_on), MONTH(published_on)

ORDER BY YEAR(published_on), MONTH(published_on);

in mysql it returns this:


m	y

1	2010

1	2011

3	2011

4	2011

6	2011
2

I have something like this working, but wonder if there is a cleaner way


$query='SELECT MONTH(published_on) as m, YEAR(published_on) as y

	FROM pressrelease

	GROUP BY YEAR(published_on), MONTH(published_on)

	ORDER BY YEAR(published_on), MONTH(published_on);';

$menu=Yii::app()->db->createCommand($query)->queryAll();