Adding custom function to doctrine query builder group by clause

Question

I want to group data by year and month of a date column using doctrine. It currently uses the query builder to produce the statement which is working fine apart from the grouping. I have installed the Month and Year custom functions from the Doctrine Extensions pack, however, I cannot do the following:

$qb->add('groupBy', 'MONTH(i.instdate)');

I get an Error: Cannot group by undefined identification variable message.

Is this possible with the query builder? If not can I add DQL to a query builder result? What is the best way to do this? I don't want to change the whole system to DQL as it is a query built from form options on the fly, so that would be a major change.

Answer 1

There is a workaround that you can use if you can add your custom function to your select clause. You can group by an alias of a custom function result that is in your select clause.

This would look like this

$qb->select('MONTH(i.instdate) as myMonth'....);
$qb->groupBy('myMonth');

Answer 2

It appears that grouping by functions is not possible in the Doctrine version I am using. It is available in later versions.

I decided to use SQL statments when this was required instead, as changing to a different version of Doctrine, inside ZF this close to a project completion would be too much.