Get it All
Together

Another post in my Development Walk-Through Series, following me through the development of my own application to share my thoughts and observations. Please consider subscribing to my feed if you find this information helpful.

Now that you’re working on your Models in Phase Two of your development process, you have a second opportunity to trim up your database. Once you start putting together CakePHP queries, it quickly becomes obvious that CakePHP will arrange your queries according to it’s own sense of MySQL rather than what you might have originally thought. This means that the database development you did in Phase One will have to be altered a bit.

But the way you put together queries is also important. For example, when assembling the [‘conditions’] array within your query, the order in which you specify the conditions is directly reflected in the way the conditions are built into the query.

Why is this important? Well for a start, because if you’re using any multi-column indexes, those indexes rely on the order of columns in the query being in the same order as they appear in the index. Without the correct order, they simply cannot be used. I don’t have any proof to back this up, but it stands to reason that this fact may point the way to another tweaking possibility: that the order of columns probably matters regardless of whether or not you use multi-column indexes.

The take away from this is that when you create queries in the CakePHP manner, it’s important to look at the resultant query and make sure it’s behaving the way you expected it to. If you can tweak the query, do it. If you cannot, it’s probably time to rethink some of your optimization strategy to better reflect the reality of the system in which you are working.

If you’ve been reading through my Development Walk-Through Series, this is Part II. Part I can be found here.

I’ve spent long hours tweaking my database, reading up on relatively arcane MySQL tuning techniques, filling the database with dummy values, querying, checking, requerying. I’m reasonably certain that the database structure I’ve chosen is the best option for my application. At least, that’s the way it appears for now: databases always change over time, so it’s worth keeping a loose hold on your concept of “acceptable.” Now it’s time to start putting together the CakePHP side of the show.

And what’s the first step? Well, once you’ve uploaded your files and established a connection to the database, it’s time to start building the models[1. The process for uploading and setting basic configuration of a CakePHP installation is well-documented and we shan’t go into detail here.]. Models are the CakePHP expression of your database structure and relationships. By properly building and configuring your database model files, you can speed the development of new database queries by allowing CakePHP to do a lot of the lexical heavy lifting. Instead of spending time getting frustrated trying to figure out what’s wrong with this code:
[code]
SELECT ratings.rating ratings.category
FROM `ratings_entries`
JOIN `ratings` ON ratings.rating_id = ratings_entries.rating_id
WHERE ratings_entries.cmp_id = %cmp%
AND ratings_entries.zipcode = %zip%
AND ratings.cat_id = %cat%"[/code]
You can simply create the relationship in rating.php, add the WHERE conditions to an array of variables and let CakePHP build the query for you[2. By the way, the problem with the example MySQL is that there is a missing comma (“,”) between ratings.rating and ratings.category].

But there is an inherent difficulty and almost contradiction to the way CakePHP’s documentation explains building your first app. Namely, the CakePHP docs show you how to build a model, then a controller and finally three separate view files for each individual database table. This is a simple setup aimed at producing a working model, not an optimal example. For a start, you may not necessarily need to use controllers or views for every single database table. Beyond that, it is not at all practical to have to create a model, controller and view for every single table just to find out if the models you’re building are working or not. The contradiction is that, while models, controllers and views should be independent, the example forces them into an uncomfortable marriage.

The solution for your model testing needs is to create one controller and use the $uses variable to its best advantage, including all the models you need into a single location. This may be – and almost certainly will be – entirely too many models for any one practical controller in your application. But this is a short-cut method aimed at bringing in all your data models and working with them. Whereas the example application shows you the following basic configuration:
[php]var $uses = array(‘Ratings’);[/php]
You can instead load all your models into this single controller:
[php]var $uses = array(‘Ratings’,’RatingEvents’,’KitchenSink’);[/php]
Now you can test your basic queries and make sure they respond the way you expect. You can have a quick check for lexical errors in your PHP without having to build three other PHP files before you get started. In short, you can develop just the models and leave the controller and view development where they belong: in the controller and view development steps.