We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

ORM - Group by month

Hello,

Is it possible to group records by month from specyfic column?

$reports = Reports::find(array( "group" => "MONTH(create_date)" ));



10.8k

I've never tried it but I would expect just passing it as a string should work.

Reports::find("GROUP BY MONTH(create_date)");


6.4k
Accepted
answer

The solution I've found for this type of queries is to select using a MySQL query alias with AS or refer to the model FQCN, PHQL has a weird behavior sometimes... here is how I do it:

$reports = $di['modelsManager']->createBuilder()
    ->from(['r' => Reports::class])
    ->groupBy('MONTH(r.created_at)')
    ->getQuery()
    ->execute()
;

Or if you want to use Model::find():


$reports = Reports::find([
    'group' => 'MONTH('.Reports::class.'.created_at)'
]);

If you are not using namespaced model classes is not needed to use Reports::class, simply Reports

Thanks, excelent solution!

I've found different. Using "columns" and format date but than we got array instead of objects. http://forum.phalcon.io/discussion/6313/query-builder-with-group-causes-error