[FIXED] Cakephp3, How to use where clause and inner join in cakephp3?

Issue

I'm trying to get review tables from product table. and I added column name 'delete_yn' which means the review is deleted or not. I used some cake query below

return $this->Product->find()
        ->contain(['ProductReview', 'Users'])
        ->where(['Product.product_code' => $productCode])
        ->toArray();

and the result is this fine.

However, now I want to check if the review was deleted by a user and show only 'n' in the column 'del_yn' I added this query

->andWhere(['Product.ProductReview.del_yn' => 'n'])

after where clause.

But it does not work.

Please Help.

Solution

Instead of using andWhere(['Product.ProductReview.del_yn' => 'n']) , use matching (if cakephp 3.0.x) or innerJoinWith() if(cakephp 3.1.x) and filter records matching specific associated data. For example,

return $this->Product->find()
            ->matching('ProductReview', function ($q) {
               return $q->where(['ProductReview.del_yn' => 'n']);
            })
            ->contain(['ProductReview', 'Users'])
            ->where(['Product.product_code' => $productCode])
            ->group('Product.id')
            ->toArray();

Answered By - Yakng