Sunday, March 28, 2010

find() using multiple references to the same table

Hi,

I have a rather complicated relationship involving a table being
referenced twice by two different tables, and I can't get find() to
put the conditions on the correct instance of the double-referenced
table.

Specifically, I have Applications, which have a Student and a School
(the school the student is applying to), and Students have a school
which is the school they're currently at. I'm currently working with
an Applications controller/view.

I am successfully using a condition on Applications.school_id to limit
the results to only applications relevant to the school being applied
to, but would also like to add a condition limiting the school the
student is currently at by name. I tried $options['conditions']
['Student.School.Name'] but it didn't like that.

Here's the SQL I would write if I were doing this outside cakephp,
hopefully this will make the above clearer. It's the third JOIN/second
half of the WHERE clause that won't work:

SELECT * FROM applications
JOIN schools as appliedToSchool ON (applications.school_id =
schools.id)
JOIN students ON (applications.student_id = students.id)
JOIN schools as fromSchool ON (students.school_id = schools.id)
WHERE applications.school_id=### AND fromSchool.name = 'SSS';

This doesn't seem too complex, so I'm probably missing something
obvious. Thanks for any advice you can give.

Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en

To unsubscribe from this group, send email to cake-php+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.

No comments: