TyCamTech.com

If you've done enough projects, chances are you've had some pretty big table join queries getting a wide range of data from various tables all one time. For the most part, this has never been a problem and shouldn't be a problem still... if you keep one thing in mind; order matters!

It happened to me and so I hit google, the #cakephp chat room, text books and more and found no real concrete answers but I also discovered that a lot of people are experiencing this issue after upgrading MySQL to version 5.

The issue arrises due to CakePHP's auto joining tables using the Model relations that you specify in the model files being joined after you add your own joins to the query you wish to run. That means that if you make a query, include 2 table joins in the query, it'll join those tables and THEN go to the model and find any relations to also join. This may result in a join trying to access a column's information before the column's table has been joined and thus, throwing an error.

There are a couple of ways to avoid this... 1. The first way to avoid this happening is to make sure you reference the correct model in the first place. In my case, I had a 'photos' table which called a table containing the people in the photos and also a release table which held all of the release information, such as the schedule, order and so forth.

I had a query checking the release table for releases, then that table would go get the photos for the release. That was fine but I had a join in my query to go get the actors that were in the photos. This was joined based on photo_id which, because the model's relations were not yet run, would cause MySQL errors due to it not being joined yet.

The solution to this is to run the query against the photo model to begin with, which will auto join both the releases and the actors entirely on it's own anyhow.  The key is to think about what you are retrieving (photos vs a schedule, because it's photos you're showing) and then flow chart it from there. Start at the top and work you're way down, don't start in the middle and have to branch outwards in 2 directions.

2. The other way to avoid this issue is to set recursive to -1 and do your own joins in your own order. This renders CakePHP's model relations useless but hey, it's an option. If you have a rather large query that simply can not be ordered correctly due to CakePHP injecting it's joins at the end every time, then you have the option of doing your own joins in your own order by telling CakePHP not to do it itself.

Site wide, this could lead to a LOT of extra code writing on your part but you should never really need to do that. It is an option, but you should really only need to do it for specific queries that just won't listen to reason otherwise.

Until CakePHP comes up with an intelligent way of determining the join order on it's own, or MySQL decides to give us the option to turn off the join order requirement... you'll just have to keep it in mind and work it out yourself.