debuggable

 
Contact Us
 

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

Posted on 11/6/08 by Tim Koschützki

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;

You can skip to the end and add a comment.

dr. Hannibal Lecter said on Jun 11, 2008:

As father Ted Crilly would say, fecking marvelous!

Time to reconsider my search queries.. ;-)

Matthew Nessworthy  said on Jun 11, 2008:

Awesome!

Will this work with the Model's find function...

e.g.
&this->find(

'all',

array(

'group' => array(

'Product.name'

)

)

);

Tim Koschützki said on Jun 11, 2008:

Matthew Nessworthy: Currently no arrays are supported, only strings. However, we could add that maybe.

Matthew Nessworthy  said on Jun 11, 2008:

Thanks Tim, i think it would be great to implement and it would also keep it consistant with the find() function's way of doing things.

GreyCells  said on Jun 11, 2008:

This is great, Mark and Tim - are we likely to see an accompanying implementation of 'HAVING' ('GROUP BY's second cousin)?

+1 to allowing arrays - just for consistency - all other args in this area seem to accept either string or array.

Whilst we're Thinking of the Children - any chance of sneaking in 'lock' => column|table name so that 'FOR UPDATE OF ...' can be issued? :)

Nate Abele said on Jun 11, 2008:

GreyCells: At this point there's really not much in the way of demand for that stuff, so we probably won't get to it any time soon. But in and of itself, it should be fairly easy to accomplish by extending a database driver.

Tim Koschützki said on Jun 11, 2008:

Okay will implement the array functionality tomorrow.

GreyCells  said on Jun 11, 2008:

Nate: Whadayamean, there's no demand? - I'm one of the most demanding people around... (allegedly)

I admit HAVING is not so commonly used except by those of us who have databases in our bones, but when you need it, you need it - and it's a natural companion to 'GROUP BY'.

But the 'lock' would allow pedants like me to achieve true ACID updates - e.g. at the moment, even in an optimistic/pessimistic locking setup, there is still a risk of data becoming out of date in that (admittedly short) period between SELECT and UPDATE.

I'm sure extending a database driver is easy for a man skilled in the arts, but one man's 'easy' is another man's 'Ooops!'...

Tim: The Children will love you forever (well, at least 'til you run out of chocolate).

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.