Most concise and complete Recursive Association Support in CakePHP?

Question

I'm currently mucking about with CakePHP, deciding if I'll use it in an upcoming web application.

The problem is, I've got several tables which at some point share relevant data with each other. If I were to write all the code myself I would use an SQL query using rather a lot of different joins and subqueries. But from what I understand CakePHP only supports joins between two tables.

So for example, I have Users, Profile, Rank, Rating tables and I want to get the profile, rank and ratings of one particular user. CakePHP will do the trick by using multiple, separate SELECT statements. But this would be possible using one query with multiple joins. Performance is expected to be quite important, so not being too wasteful with SQL queries is a major perquisite.

I've found two hacks (one behaviour and one using bindModel) and a similar StackOverflow thread.

I'm undecided whether to use the behaviour or the bindModel hack. Could anybody shed any light as to what is the best approach - viz. what integrates best in the overall CakePHP structure (are features like pagination still available)? Or is there another approach which is ultimately better. The SO thread mentions a method using containables.

Hope I'm not wrong in opening a separate question for this, but the older thread lists some solutions, but the answer isn't that clear to me for the aforementioned reasons.

Answer 1

I have had similar problems and because performance was a huge factor I decided to simply use raw SQL rather than try and fiddle with solutions purely to maintain "cake-ness". Plus sometimes it's just nice to know where the bottleneck is (even though a debug mode of 2 does help somewhat). Migrating the db won't ever be an issue.

I decided to go for performance over the convenience of auto-pagination, sorting etc. Really you can code these yourself - you did so in the past I'm sure.

The bindModel solution however does interest me. This is what I would go for next time I come across this problem.

Answer 2

The link to this bakery article in the other StackOverflow article you mentioned, is probably the better method for doing ad-hoc joins (without bindModel or a custom behavior). You can already specify joins inline (including extra tidbits such as the type of join) in the options for any find() method calls, but those can be greatly simplified by creating a new find "type" that requires less writing in the find() options. That's what the article discusses.

I also used to use raw SQL for some queries, but found that it can lead to unforseen incompatibilities with databases that are supported by CakePHP. However, this may not be much of an issue if you are not writing a web application to be used by the masses.

Answer 3

The easiest way to do this is to not bother with reducint the sql queries and to implement some form of caching.

The next solution - skipping over the Containable behavior since it doesn't work to reduce your queries is to do some ad-hoc joins in the find calls directly. Pushing these into the model so that you can call them from a central place is recommended. The good article on this technique is on the bakery here: http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find

The best solution I have found to date is Rafael Bandeira's Linkable behavior : http://blog.rafaelbandeira3.com/2008/11/16/linkable-behavior-taking-it-easy-in-your-db/ which allows you to use a custom key in the options array that defines the fields and relationships to join on in a clear fashion and uses the technique described in [1] to use joins instead of sequential queries.

Good luck with your project.