I agree with John, And I would personally use a join query in this type of situation. Your most likely not going to need all of the data that comes back from normal cake binding. And normal cake binding is going to perform multiple queries to obtain the data (this is not a bad thing, but it does need to be understood). I've found anytime your digging that deep into the data relations your also going after a fairly narrow slice of the data. Joins will give you the exact data you need with a minimal number of queries.
I've had a couple situations where I needed all the data from the deepest relation. In this case I made a design change at the view level and was able to break up that information into page-able sets so the amount of data from any one query was limited. I use to get quite frustrated with the number of queries and binding/ unbinding etc that was needed to get the data out of the models. To the point of often writing functions in the model that would build a sql select and execute it with a call to query(). I finally took the time to really read and understand both containable, and how joins work and have since gone back through the code and replaced all the query calls with properly structured options arrays and find(). While reading about Containable keep in mind it is primarily a wrapper around bind/unbind. So containable probably won't help you much for performance on this issue as it will still generate many queries (probably more than 144 separate queries in your case). See this article which explains the problem with using containable in deep relations. http://www.endyourif.com/cakephp-containable-statement-pitfalls/ I still build up and test my sql statements by hand, then I figure out weather I should use containable, joins, or both in some cases. When at all possible I prefer to use containable because it is very simple to read and understand. A couple of questions to ask yourself when using joins is: "Are we joining the data to filter the data or are we joining the data to display some fields of the joined data? Do we need to display fields from both/multiple tables in the join?" These questions are somewhat important because of the way joined data is returned in the array. You will want to write your join queries so the table which you want to display fields from is the first table and then join the additional tables for filtering. In other words its not always a top down select, sometimes the data you really want is at the bottom and you only need the parent model for filtering. On Jun 8, 7:01 am, John Andersen <[email protected]> wrote: > This is a frequent topic, retrieving data from models over several > levels of association. > > The current answers nearly always are: > > 1) Look into using the Containable behavior, > see:http://book.cakephp.org/view/1323/Containable > > 2) Look into using joins, see:http://book.cakephp.org/view/1047/Joining-tables > > The above should make it possible for you to avoid the additional > queries. > Enjoy, > John > > On Jun 8, 1:10 pm, pinker42 <[email protected]> wrote: > > > Hi, > > > i urgently need some help with a really nasty problem. > > > I have 4 main tables A,B,C,D and two join tables AB, BC joined: A (1 > > record) -> (HABTM AB) B (13 records) -> (HABTM BC) C (131 records) -> > > (hasMany) D (405 records). When i fetch the data with A->find('All') > > cake does a good job for A,B and C. But than repeats the Query for D > > 13 times (number of records for B !). > > The result set is okay, but the performance is really sad, because the > > result set for D is quite Huge. Is it not possible to handle > > MODEL->HABTM->HABTM->HasMany releationships with simple model binds in > > > Cake ? > > > Best regards and thanks in advance > > > Dirk Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
