zen of coding

CakePHP and custom SQL

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.

 

  • I can illustrate this with a simple example: how to fetch the rolling sum (i.e, accumulated count) of all new records in a table until a given date?

    I haven’t found an easy way to do this with the custom find() method, so I use this custom query to chart both new users and the total user count for a given date daily:

    SELECT a.day AS day, a.new AS new, SUM(b.new) AS total
    FROM (SELECT DATE(created) AS day, COUNT(*) AS new FROM users GROUP BY day) AS a
    LEFT JOIN (SELECT DATE(created) AS day, COUNT(*) AS new FROM users GROUP BY day) AS b
    ON a.day >= b.day GROUP BY a.day ORDER by day ASC;

  • teknoid

    @Jaime Gómez Obregón

    Sure, there is a time and place for custom queries, I’m encouraging people to study the way cake builds queries and explain why one should rely on the cake-way of doing things. And also to keep in mind that custom SQL, if needed, should be in the model ;)

  • Renju

    Hi please help me..
    how to implement this custom query..you said that i have to put the code in model..but how to get the data from controller..please explain the custom query with simple example…please..

  • @Renju

    First off, what is the query you are talking about?

    The way you usually do it, is you build a method in the model like… getSomeData();, in which you would add your required query, and return the result.

    Then your relevant controller you would do: $someResult = $this->ModelName->getSomeData();

    Please check at the google group for more details, this is really not a help forum ;)

  • Pingback: Tagz | "Cakephp" | Comments()

  • Pingback: Tagz | "Cakephp" | Comments()

  • Pingback: nothing happens :: when magic gets in the way()

  • Good ,Really Good

%d bloggers like this: