Hi there Thanks for your answers.
I am not that worried about the number of queries although the 20 was just an example. Although on the index page of my website I would have around 200 extra queries which (during testing) sometimes causes a page delay of about half a second - not a huge problem but not very good. The bigger problem is that I cannot have the query conditions that I want (with some conditions being in the first model, some in the second and some in the third). I'll have a look at the dbo-patch the AD mentioned in the reply as this seems to solve this problem. Thanks again Claudia On Nov 6, 10:09 am, AD7six <[EMAIL PROTECTED]> wrote: > On Nov 5, 4:33 pm, Claudia <[EMAIL PROTECTED]> wrote: > > > > > Hi there > > > I have three models that are chained with belongsTo in one direction > > and hasMany in the other direction: > > Model1 belongsTo Model2 belongsTo Model3 > > and > > Model3 hasMany Model2 hasMany Model1 > > > Now I need to query model1 and also get the related data from model2 > > and model3 which can be easily achieved by settings model1->recursive > > = 2 and then use model1->findall. > > The resulting SQL ( I use postgres but mysql should be similar): > > Cake makes a join on the model1 and model2, keeps the resulting ids of > > the entries in model2, then makes a query for each of these ids on > > model2 and a query for the related model3. > > > This causes two problems: > > a) I cannot restrict the result depending on some columns on model3 as > > the first query fails with 'missing from-clause entry for table > > model3'. > > Some interesting > reading:https://trac.cakephp.org/ticket/633http://lloydhome.com/blog/archives/2007/02/Recursive-Association-Supp... > > > b) The query is really inefficient: Example: If the first query > > returns 10 results we need 21 queries for in total (the join query and > > then 10 queries each for model2 and model3). We could get the same > > result by having only one query which uses a left join for joining > > model1 and model2 and another left join for joining model2 and model3. > > > I have found a trac issue regarding this but it was closed as > > wontfix:https://trac.cakephp.org/ticket/2931 > > Reducing the number of queries isn't the be all and end all. as > clearly stated in that ticket if you attach a patch to it it'll be > marked as an enhancement and considered. > > hth, > > AD --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" 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 -~----------~----~----~----~------~----~------~--~---
