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 mdc...@gmail.com 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 j.andersen...@gmail.com 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 kuepper...@googlemail.com 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
