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.

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