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:
- beforeFind()/afterFind() and beforeSave()/afterSave() methods
- CakePHP will make your data safe for insertion and generally will sanitize your SQL
- Model recursivness
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.
