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
