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

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:

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

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

$query = trim(low($query));
$conditions = array(
'or' => array(
'LOWER(Product.name) LIKE' => "%{$query}%",
'LOWER(Product.description) LIKE' => "%{$query}%",
'LOWER(Product.sku) LIKE' => "%{$query}%"
),
'active' => 1
);

$this->paginate['Product']['conditions'] = $conditions;
$this->paginate['Product']['group'] = 'Product.name';
$products = $this->paginate('Product');

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

$order = array('created' => 'desc', 'name' => 'asc');
$group = 'name, created';
$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

&nsbp;

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

(Theoretically) Related Posts