Re: find() using multiple references to the same table

You have to use the table alias for the schools table! So your joins
become this:

SELECT *
FROM applications AS Application
JOIN schools AS AppliedToSchool ON (Application.school_id =
AppliedToSchool.id)
JOIN students AS Student ON (Application.student_id = Student.id)
JOIN schools AS FromSchool ON (Student.school_id = FromSchool.id)
WHERE AppliedToSchool.id = ###
AND FromSchool.name = 'SSS';

That makes sure that you do not refer to the same school as both the
AppliedToSchool and FromSchool, which would never return anything -
unless the student applies to the same school :)

Enjoy,
John

On Mar 29, 1:00 am, Ryan <ryan.ravennasoluti...@gmail.com> wrote:
> 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.