I've recently had the same problems with Containable, it will execute tons of queries for models that are directly related and could be joined. Not only does this result in a query overhead; you also (in some cases) lose the ability to apply conditions to the joined models, very inconvenient. I cannot tell you how to solve it in a way that's exactly clean, but I do think I might have a workaround for your case.
What I figured out is that this problem only seems to occur once you go 'deeper' than one level in your contain (or at least it's a theory I've seen confirmed in a couple of my own experiments). You can test that by removing the "City" and "Country" associations from your contain, I think you'll see that you suddenly have only one query left. I don't think you can avoid doing going this deep in your query though if you need City and Country, you could query Student instead of Registration and contain Registration to that, but I suppose you'd have to contain CourseTracking and Course in that one and end up with the same problem. You could perhaps establish a temporary direct relation between Course, CourseTracking and Student without a foreignKey, using presence of Registration as a join condition. Or the other way around if you're querying Registration; establish a direct relation between Registration and City/Country using Student.country_id and Student.city_id as join conditions, since you're sure they'll be there in this query. All of this shouldn't be necessary and I certainly hope they'll solve this in future versions, it's certainly a problem for those of us who are looking to keep the number of queries to a minimum, and especiallly for those who'll end up with slow results because they don't look at the query log and think Cake will just get it right. On May 4, 1:22 pm, jmn2k1 <jmn...@gmail.com> wrote: > Hi, > > I need to make some report of my multiple associations, I tried with > containable and was surprised as it resolve to bring all the data with > the proper joins, however still do multiple queries for a model that > is already joined. > > $params = array( > 'fields' => array( > 'id', 'student_id', 'course_id', > 'registration_status_id', > 'registration_course_status_id', 'progress' > ), > 'contain' => array( > 'Course' => array( > 'fields' => array('id', 'title') > ), > 'Student' => array( > 'fields' => array('id', 'firstname', 'lastname'), > 'City', > 'Country' > ), > 'CourseTracking' => array( > 'fields' => array('started', 'finished') > ) > ), > 'conditions' => array( > > ), > 'order' => array('Student.lastname') > ); > $this->Course->Registration->find('all', $params); > > I have the Registration model which is a link table between Course and > Student, and the generated query is: > > SELECT `Registration`.`id`, `Registration`.`student_id`, > `Registration`.`course_id`, `Registration`.`registration_status_id`, > `Registration`.`registration_course_status_id`, > `Registration`.`progress`, `CourseTracking`.`started`, > `CourseTracking`.`finished`, `Course`.`id`, `Course`.`title`, > `Student`.`id`, `Student`.`firstname`, `Student`.`lastname`, > `Student`.`country_id`, `Student`.`city_id` FROM `registrations` AS > `Registration` LEFT JOIN `courses` AS `Course` ON > (`Registration`.`course_id` = `Course`.`id`) LEFT JOIN `students` AS > `Student` ON (`Registration`.`student_id` = `Student`.`id`) LEFT JOIN > `course_trackings` AS `CourseTracking` ON > (`CourseTracking`.`registration_id` = `Registration`.`id`) WHERE > course_id IN (1, 2) ORDER BY `Student`.`lastname` ASC > > Which have all the required data, however the sql dump show me one of > the following queries for every registration: > > SELECT `Course`.`id`, `Course`.`title` FROM `courses` AS `Course` > WHERE `Course`.`id` = 2 > > The strange part is that Course and Student are associated exactly in > the same way. Any hint on how to get rid of those queries? -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php