multi level association and filter in cakephp

Question

I'm new to stackoverflow and this is my first question. Please pardon me for any errors in question.

I'm working in CakePHP 3.2

I have categories, products, seller_products table and their association is like CategoriesTable.php

$this->hasMany('Products', [
   'foreignKey' => 'category_id'
]);

ProductsTable.php

$this->belongsTo('Categories', [
     'foreignKey' => 'category_id',
     'joinType' => 'INNER'
]);
$this->hasMany('SellerProducts', [
    'foreignKey' => 'product_id'
]);

SellerProducts.php

$this->belongsTo('Products', [
    'foreignKey' => 'product_id',
    'joinType' => 'INNER'
]);

Table Association

products table has category_id as foreign key referencing categories table and seller_products has product_id as foreign key referencing products table

seller_products has column stock.

What I want to do is to select all categories containing associated products whose stock in seller_products is > 0

This is what I have done

$pros1 = $this->Products->Categories->find()
  ->where([
     'Products.SellerProducts.stock >' => 0
  ])
  ->contain([
     'Products.SellerProducts', 'Subcategories', 'ProductTypes'
  ]);

but it is giving error as

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Products.SellerProducts.stock' in 'where clause'

multi level association and filter in cakephp

0 Answers0