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

Thanks for the reply.

I'm sure I can make it work with an SQL query, but I'm new to Cake and
I'm trying to work within the system :)

Is there a way to make this complex a query work with conditions on
find(), paginate(), etc, or will I need to resort to writing custom
queries?

~Ryan

On Mar 29, 11:18 am, John Andersen <j.andersen...@gmail.com> wrote:
> 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.