[FIXED] Cakephp 3 NOT IN query

Issue

I think this is a common pattern, but I can't find the elegant CakePHP way of doing it. The idea is to remove the values from a list which have already been chosen. To use an example from the Cake book:

table Students (id int, name varchar)
table Courses (id int, name varchar)
table CoursesMemberships (id int, student_id int, course_id int, days_attended int, grade varchar)

All I want to do is create a query which returns the courses that a given student has not yet signed up for, most probably to populate a select dropdown.

If I weren't using Cake, I'd do something like

select * from Courses where id not in 
    (select course_id from CoursesMemberships where student_id = $student_id)

Or maybe an equivalent NOT EXISTS clause, or outer join trickery, but you get the idea.

I'm stumped how to do this elegantly in Cake. It seems to me that this would be a common need, but I've researched for awhile, as well as tried some query ideas, to no avail.

Solution

Found IMO the most elegant answer... use the notMatching() option:

$data = $this->Courses->find("list")
                      ->notMatching("Students", 
                         function($q) use ($student_id) {
                            return $q->where(["Students.id"=>$student_id]);
                         }
                      );

This assumes that Students HasMany Courses and Courses HasMany Students of course.

I think this is the most elegant answer since it doesn't depend on knowing any SQL, and represents the logic of what I'm trying to achieve using Cake's semantics only.

Answered By - Roger Kaplan