cakephp 3 matching not working

Question

I have categories, products and seller_products table and want to select all from three tables group by categories where seller_products.stock > 0 and seller_products.status = 1

This is my code

$pros1 = $this->Products->Categories->find()
      ->where([
    ])
->select(['Categories.id', 'Categories.title'])
->distinct(['Categories.id'])
->contain(['Products'])
->matching('Products.SellerProducts', function(\Cake\ORM\Query $q) {
   return $q->where(['SellerProducts.stock >' => 0, 'SellerProducts.status' => 1]);
});

But this is not working. I'm getting all products group by categories by matching is not working and still getting products whose stock is 0 or status is not 1

Their associations are

$categories->hasMany('Products');
$products->hasMany('SellerProducts');

Answer 1

You need to understand that contain() and matching() are different. You are asking for categories having products where there is stock. But if you want what products are those that matched, you need to filter them in contain() as well:

$matcher = function(\Cake\ORM\Query $q) {
   return $q->where(['SellerProducts.stock >' => 0, 'SellerProducts.status' => 1]);
};


$pros1 = $this->Products->Categories->find()
    ->select(['Categories.id', 'Categories.title'])
    ->distinct(['Categories.id'])
    ->contain(['Products' => function ($q) use ($matcher) {
         return $q->matching('SellerProducts', $matcher);
    }])
    ->matching('Products.SellerProducts', $matcher);