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 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 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
>
&
