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
  1. $result = $Thread->find('all', array(
  2.   'conditions' => array('Thread.project_id' => 1),
  3.   'group' => 'Thread.project_id, Project.id')
  4. );
  5. $this->assertEqual($result, $expected);
  6.  
  7. $result = $Thread->find('all', array(
  8.   'conditions' => array('Thread.project_id' => 1),
  9.   'group' => 'project_id')
  10. );
  11. $this->assertEqual($result, $expected);
  12.  
  13.  
  14. $result = $Thread->find('all', array(
  15.   'conditions' => array('Thread.project_id' => 1),
  16.   'group' => array('project_id'))
  17. );
  18. $this->assertEqual($result, $expected);
  19.  
  20.  
  21. $result = $Thread->find('all', array(
  22.   'conditions' => array('Thread.project_id' => 1),
  23.   'group' => array('project_id', 'Project.id'))
  24. );
  25. $this->assertEqual($result, $expected);
  26.  
  27.  
  28. $result = $Thread->find('all', array(
  29.   'conditions' => array('Thread.project_id' => 1),
  30.   'group' => array('Thread.project_id', 'Project.id'))
  31. );
  32. $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
  1. $result = $Thread->find('all', array(
  2.   'conditions' => array('Thread.project_id' => 1),
  3.   'group' => array('Thread.project_id', 'Project.id'))
  4. );

would result in a GROUP BY statement that looks like:

php
  1. 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