How to do Group By conditions in Model find() calls in the CakePHP RC1

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

Hey folks,

we have to thank Mark Story and wluigi for working on group by conditions for Cake's Model::find() method. Up until now, you would have to add your GROUP BY stuff to string'ed conditions in order to support a group by statement, which could be very ugly.

Have a look at the following:

php
  1. $query = trim(low($query));
  2. $conditions =
  3.     "Product.active = '1' AND (LOWER(Product.name) LIKE '%{$query}%'
  4.     OR LOWER(Product.description) LIKE '%{$query}%'
  5.     OR LOWER(Product.sku) LIKE '%{$query}%') GROUP BY Product.name";
  6. $this->paginate['Product']['conditions'] = $conditions;
  7. $products = $this->paginate('Product');

Now with the new worked in group key you would just do:

php
  1. $query = trim(low($query));
  2. $conditions = array(
  3.   'or' => array(
  4.     'LOWER(Product.name) LIKE' => "%{$query}%",
  5.     'LOWER(Product.description) LIKE' => "%{$query}%",
  6.     'LOWER(Product.sku) LIKE' => "%{$query}%"
  7.   ),
  8.   'active' => 1
  9. );
  10.  
  11. $this->paginate['Product']['conditions'] = $conditions;
  12. $this->paginate['Product']['group'] = 'Product.name';
  13. $products = $this->paginate('Product');

Much cleaner! Cool! Now let's see what we'd do if we wanted to group by over several columns:

php
  1. $order = array('created' => 'desc', 'name' => 'asc');
  2. $group = 'name, created';
  3. $products = $this->paginate('Product', compact('order', 'group'));

The group statement currently works as a string. So you would have to separate multiple group by fields by a comma.

Kudos to wluigi and mark_story. Checkout the Changeset if you are interested in the underlying implementation.

Now who is not excited about the new release?

-- Tim Koschuetzki aka DarkAngelBGE