This is how you’d write a BETWEEN… AND… SQL query in a cake-like way.
The example should be pretty much self explanatory:
$this->Post->find(‘all’, array(‘conditions’=>array(‘Post.id BETWEEN ? AND ?’ => array(1, 10))));
Note, that CakePHP will quote the numeric values depending on the field type in your DB.
Just recently ( end of May, 2008 ) there was an addition to CakePHP’s find method to easily build a GROUP BY in your SQL.
If you’ve been using something like $this->Product->find(‘all’… etc., etc. Now you can add a new key ‘group’ to your find method, like so:
$this->Product->find(‘all’,array(‘fields’=>array(‘Product.type’,'MIN(Product.price) as price’), ‘group’=> ‘Product.type’));
Of course you will have to upgrade to a nightly SVN core for this to work.
Let’s say you need to know when was the very first user record created.
Add a method like this to your User model (assuming you have a ‘created’ field):
[sourcecode language='php']
function getFirstDay() {
return $this->find(‘first’, array(
‘fields’ => array(‘created’),
‘order’ => ‘User.created ASC’));
}
[/cc]
Try in the controller: pr($this->User->getFirstDay());
Similar method can be used to get information about any other arbitrary field.
If you’ve been around CakePHP for some time, you’ve probably heard that writing custom SQL is pretty much frowned upon. At the core, CakePHP provides some clever ways to write queries by employing the find() and save() methods, yet in some cases it is just impossible to use find() or save() to get Cake to build the query you need.
So what happens then and what is the big deal about custom SQL?
Well, first remember that cake’s goal is to make your life easier. Therefore find()/save() methods do a lot more than just build the queries for you and therefore save you some typing. Remember the following benefits of using CakePHP’s find()/save() methods:
Having CakePHP take care of all of the above, is not only life-saving at times (such as safe SQL) and convenient (getting associated model data), but it also allows you to write clean, robust and easily manageable code and it promotes good coding practice. You can forget about all that when writing custom SQL by using Model->query().
Well, if you’ve spent at least a few hours banging your head on the wall and simply cannot find a way to build your query in a cake-like manner, then be mindful of what you are doing with the SQL and always remember to keep your data safe.
And to wrap it up, don’t forget that as a rule of thumb… all custom SQL should be in the model.
