How to convert SQL to CakePHP find

There are a lot of newcomers asking the same question on CakePHP's IRC channel, Stack Overflow and various other places, and that question is how to convert a SQL query into the corresponding CakePHP code.

Once you have been using CakePHP for a while the ORM becomes second nature and its pretty easy to see how a query will fit into a Model::find(). I normally break the SQL up into the various sections such as the fields, joins, conditions, group and limit. Once you have the various pieces they just need to be slotted into the find code.

The other problems newcomers often face is how to access the model from various parts of the application. This differs slightly for Controllers, Models, Components and libs.

To try help with all these questions I have built a script that parses SQL statements and generates the corresponding CakePHP code. Currently it does find() queries only but I plan to add more support for others such as save(), updateAll() and delete().

There are a couple of options to play with such as where the query is called from, the type of find to use and so on. The conversion will also attempt to clean up the query and use the correct aliases where possible.

Have a look at a few examples with a basic query and various options:

So based on the options the code differs slightly, including some finer details such as the correct loadModel() call when used in unrelated controller or using ClassRegistry::init() when called from a lib class.

Right now there is nothing to specify what the aliases will be so this is based on the table names using the Inflector class. Check this for more on how the Inflector works. There are some more detailed docs and information available over here

Query converter