CakePHP 3 Find records that have no associated record (hasMany)

Question

I have Products hasMany Tasks.

What is the the best way to find all products that do not have an associated record in the tasks table?

I've tried with:

$query->matching('Tasks', function ($q) {
return $q->where(['Tasks.product_id' => NULL});

But that doesn't seem to do the trick.

Answer 1

I advice you to use Subqueries

It's the simplest way to to find all Products that do not have an associated record .. Try this :

    $matchingTasks= $this->Products->association('Tasks')->find()
        ->select(['product_id'])// id of product in Tasks Table
        ->distinct();

    $query = $this->Products->find()
        ->where(['id NOT IN' => $matchingTasks]);
    // to debug the result
      foreach($query as $product){
        debug($product);
    }
    die();