Cakephp 3 NOT IN query

If you want to use a subquery, simply pass a query object as the condition value, like

$subquery = $Courses->CoursesMemberships
    ->find()
    ->select(['CoursesMemberships.course_id'])
    ->where(['CoursesMemberships.student_id' => $student_id]);

$query = $Courses
    ->find()
    ->where([
        'Courses.id NOT IN' => $subquery
    ]);

As an alternative there's also Query::notMatching() (as of CakePHP 3.1), which can be used to select records whichs associated records do not match specific conditions:

$query = $Courses
    ->find()
    ->notMatching('CoursesMemberships', function (\Cake\ORM\Query $query) use ($student_id) {
        return $query
            ->where(['CoursesMemberships.student_id' => $student_id]);
    });

See also

• Cookbook > Database Access & ORM > Query Builder > Subqueries
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Using notMatching
• Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data

In CakePHP 3 you can create NOT IN query like this.

$query = $Courses->find()
    ->where(['id NOT IN' => $ids]);

And in CakePHP 3 you can create IN query like this.

$query = $Courses->find()
    ->where(['id IN' => $ids]);

You can read about it in CakePHP 3 Cookbook - Creating IN Clauses.

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.