How to Group By in CakePHP's new release Part 2

Posted by Tim Koschützki, on Jun 16, 2008 - in PHP & CakePHP » DataSources, Models & Behaviors

Hey folks,

having promised it in the first post on how to do Group By in CakePHP I worked on an array() version for the group statement in Model::find() calls. So I implemented it. This is what can be accomplished now:

php

$result = $Thread->find('all', array(
'conditions' => array('Thread.project_id' => 1),
'group' => 'Thread.project_id, Project.id')
);
$this->assertEqual($result, $expected);
$result = $Thread->find('all', array(
'conditions' => array('Thread.project_id' => 1),
'group' => 'project_id')
);
$this->assertEqual($result, $expected);
$result = $Thread->find('all', array(
'conditions' => array('Thread.project_id' => 1),
'group' => array('project_id'))
);
$this->assertEqual($result, $expected);
$result = $Thread->find('all', array(
'conditions' => array('Thread.project_id' => 1),
'group' => array('project_id', 'Project.id'))
);
$this->assertEqual($result, $expected);
$result = $Thread->find('all', array(
'conditions' => array('Thread.project_id' => 1),
'group' => array('Thread.project_id', 'Project.id'))
);
$this->assertEqual($result, $expected);

As you can see you can still group via the former string method. In addition to that any combination of available fields can be used in an array to form a GROUP BY statement with values separated by comma.

So this code here:

php

$result = $Thread->find('all', array(
'conditions' => array('Thread.project_id' => 1),
'group' => array('Thread.project_id', 'Project.id'))
);

would result in a GROUP BY statement that looks like:

php

GROUP BY `Thread`.`project_id`, `Project`.`id`

You can leave out the alias of the model the find is invoked on as long as your columns aren't ambigous.

Happy baking all! Oh and for those who are interested in the code, have a look at the changeset.

-- Tim Koschuetzki aka DarkAngelBGE

Print this Post | Digg This | Stumble It | Delicious

3 Comments

Guillaume on Jun 16, 2008:

This is a great improvement to an already great improvement!
(though I can't help but find your code examples a bit strange! or perhaps i am missing something...)
Great work once again, thanks a lot.

Tim Koschützki on Jun 16, 2008:

Guillaume: Well, that's probably because it's just the tests pasted. :P

Garret Bolthouse on Jun 23, 2008:

Maybe it's just me, but for some reason instead of 'group', using 'group_by' would be more in line with cake naming conventions? Maybe I'm wrong, but it seems more intuitive for it to be named 'group_by' (for me anyway).