Hi, first of all thank you for your long and detailed answer. I'm aware of both methods John mentioned. So neither the containable behaviour nor the "forced join" solution are new to me, and i already used both techniques in the past with success.
But my problem is a bit more complicated: First of all i really need all data of all levels at once, so the containable behaviour will gave me exactly the same results and won't help me in this case. Joins are very good when used in hasOne or hasMany Relationship plus filter, but when used with HABTM releationships the result sets gets quite big, because the data of the first levels is repeated in every row. My problem is NOT, that cakes uses several queries and the IN (x,x,x,x) operator to fetch the data. The way cake builds the queries is not that slow or bad as you may think. The real problem, is that cake repeats the same query many,many times (which is still not the real problem, because the query caching of cakePHP and MySQL will prevent us from really bad things to happen) and the data is also parsed several times (this is the real performance problem, because in my case, it is a lot of data !). I created a test case yesterday and submitted a bug reportet to cakePHP, the bug was confirmed and classified as "Optimisation Bug". To my opinion the only way to fix this without the use of selfmade queries, is to cut my bindings at level 3 and load the data from levels 4 and 5 with a matching condition, and after that parse the data into one final data structure. To my knowledge the Bug is present in all 1.2.x and all 1.3.x versions. Thank you again for your help and best regards Dirk On 9 Jun., 18:42, LunarDraco <[email protected]> wrote: > 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- Zitierten Text ausblenden - > > - Zitierten Text anzeigen - 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
