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

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:

$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:

$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:

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

&nsbp;

You can skip to the end and add a comment.

Guillaume said 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 said on Jun 16, 2008:

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

Garret Bolthouse said 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).

Guillaume said on Dec 10, 2008:

hmm, now i wonder how to do the "HAVING..." part. It's not yet supported by CakePHP, is it?

Tim Koschützki said on Dec 10, 2008:

Guillaume: Yeah that is correct. What you could try is adding that to the group statement. Only an idea. I haven't tested it myself.

Annamalai said on May 23, 2009:

hmm nice , good one

This post is too old. We do not allow comments here anymore in order to fight spam. If you have real feedback or questions for the post, please contact us.

debuggable