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)" ));

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.3k
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.phalconphp.com/discussion/6313/query-builder-with-group-causes-error