CODEWITHSUNDEEP
associationscakephp-3.0has-many
timstermatic
timstermatic

Reputation: 1740

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

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.

Upvotes: 3

Views: 920

Answers (2)

50ShardsOfGray
50ShardsOfGray

Reputation: 61

As Greg Schmidt wrote: notMatching is the solution:

$query = $articlesTable->find()->notMatching('Tags');

or

$query = $articlesTable
->find()
->notMatching('Tags', function ($q) {
    return $q->where(['Tags.name' => 'boring']);
});

Upvotes: 5

kh.tab
kh.tab

Reputation: 1304

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();

Upvotes: 4

Related Questions