We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Model aggregate fields - group by multiple columns

I'm wanting to user \Phalcon\Mvc\Model::count(), but I need to group by multiple columns. Can I do that - how so?

I've tried specifying both as a string and an array:

$Group = Log::count(['meal_date = "2014-10-01"','group'=>['meal_date','meal']]);

$Group = Log::count(['meal_date = "2014-10-01"','group'=>'meal_date, meal']);


98.9k

This way:

$group = Log::count([
    'conditions' => 'meal_date = "2014-10-01"',
    'group'=>['meal_date','meal']
]);

That's just like my first example. Your code, copied verbatim, generates this exception:

Phalcon\Mvc\Model\Exception: Column 'Array' doesn't belong to any of the selected models (1), when preparing: SELECT Array, COUNT(*) AS rowcount FROM [Model\Log] WHERE meal_date = "2014-10-01" GROUP BY [meal_date], [meal]



98.9k

What version are you using? I've tested in 1.3.3 and works fine

I just upgraded to 1.3.3 before posting my last comment. 1.3.2 previously.

This is my table definition:

Field Type Null Key Default Extra
barcode varchar(13) NO PRI
meal varchar(20) NO PRI
meal_date date NO PRI 0000-00-00
username varchar(20) YES NULL
first_name varchar(255) YES NULL
last_name varchar(255) YES NULL
date_entered timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

This is my complete model:

<?php
namespace Model;

use Phalcon\Db\Column,
    Phalcon\Mvc\Model\MetaData;

class Log extends \Phalcon\Mvc\Model{

    /* Define the metadata so Phalcon doesn't have to get it every page load */
    public function metaData(){
        return[
            MetaData::MODELS_ATTRIBUTES=>[
                'barcode','meal','meal_date','username','first_name','last_name','date_entered'
            ],
            MetaData::MODELS_PRIMARY_KEY=>[
                'barcode','meal','meal_date'
            ],
            MetaData::MODELS_NON_PRIMARY_KEY=>[
                'username','first_name','last_name','date_entered'
            ],
            MetaData::MODELS_NOT_NULL=>[
                'barcode','meal','meal_date','date_entered'
            ],
            MetaData::MODELS_DATA_TYPES=>[
                'barcode'      =>COLUMN::TYPE_VARCHAR,
                'meal'         =>COLUMN::TYPE_VARCHAR,
                'meal_date'    =>COLUMN::TYPE_DATE,
                'username'     =>COLUMN::TYPE_VARCHAR,
                'first_name'   =>COLUMN::TYPE_VARCHAR,
                'last_name'    =>COLUMN::TYPE_VARCHAR,
                'date_entered' =>COLUMN::TYPE_DATETIME
            ],
            MetaData::MODELS_DATA_TYPES_NUMERIC=>[],
            MetaData::MODELS_IDENTITY_COLUMN=>FALSE,
            MetaData::MODELS_DATA_TYPES_BIND=>[
                'barcode'      =>COLUMN::BIND_PARAM_STR,
                'meal'         =>COLUMN::BIND_PARAM_STR,
                'meal_date'    =>COLUMN::BIND_PARAM_STR,
                'username'     =>COLUMN::BIND_PARAM_STR,
                'first_name'   =>COLUMN::BIND_PARAM_STR,
                'last_name'    =>COLUMN::BIND_PARAM_STR,
                'date_entered' =>COLUMN::BIND_PARAM_STR
            ],
            MetaData::MODELS_AUTOMATIC_DEFAULT_INSERT=>[],
            MetaData::MODELS_AUTOMATIC_DEFAULT_UPDATE=>[]
        ];
    }

    /* This is just a shortcut for find() */
    public static function findMeal($date,$meal){
        return parent::find([
            'conditions'=> 'meal_date = :date: AND meal = :meal:',
            'bind'=>['date'=>$date,'meal'=>$meal]
        ]);
    }

    /* This is another shortcut for findFirst() */
    public static function findEntry($barcode,$date,$meal){
        return parent::findFirst([
            'conditions'=> 'meal_date = :date: AND meal = :meal: AND barcode = :barcode:',
            'bind'=>['date'=>$date,'meal'=>$meal,'barcode'=>$barcode]
        ]);
    }
}
?>


98.9k

I've tested with just one field and it worked, however with two fields it does not work, you can use the query builder there:

$group = $this->modelsManager->createBuilder()
                ->columns(['meal_date', 'meal', 'COUNT(*)'])
                ->from('Log')
                ->where('meal_date = "2014-10-01"')
                ->groupBy(['meal_date', 'meal'])
                ->getQuery()->execute();

How would I reference the COUNT(*) column? Does columns() have an aliasing capability?

Should I file a bug report for this?



98.9k
Accepted
answer

If you want to reference it as 'rowcount', this way:

$group = $this->modelsManager->createBuilder()
                ->columns(['meal_date', 'meal', 'rowcount' => 'COUNT(*)'])
                ->from('Log')
                ->where('meal_date = "2014-10-01"')
                ->groupBy(['meal_date', 'meal'])
                ->getQuery()->execute();