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:
$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:
$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:
$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
Did you like this blog post? If so, please consider subscribing to the Blog RSS feed.
You can skip to the end and add a comment.
Awesome!
Will this work with the Model's find function...
e.g.
&this->find(
'all',
array(
'group' => array(
'Product.name'
)
)
);
Matthew Nessworthy: Currently no arrays are supported, only strings. However, we could add that maybe.
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.
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? :)
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.
Okay will implement the array functionality tomorrow.
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.
As father Ted Crilly would say, fecking marvelous!
Time to reconsider my search queries.. ;-)