cakephp 3 matching only if row exists

Question

I'm using CakePHP 3.2.

I have three tables categories, products and seller_products

I want to retrieve data from all tables, where seller_products.stock > 0 and seller_products.status = 1 also GROUP BY categories.

This code is working fine

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

an their association is

$categories->hasMany('Products', [
   'foreignKey' => 'category_id'
]);
$products->hasMany('SellerProducts, [
   'foreignKey' => 'product_id'
]);

Now, the problem is.

This query is returning even those products which does not exits in SellerProducts.product_id

How to get only those products which has existence in SellerProducts and meet the conditions as given in matching ?

Answer 1

You need to call with inner join query. Please try following code:

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

For more details, please visit http://book.cakephp.org/3.0/en/orm/query-builder.html#using-innerjoinwith