CakePHP 3 : multi level association

Question

I have 3 tables, categories, products and seller_products and their associations are like CategoriesTable.php

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

ProductsTable.php

$this->hasMany('SellerProducts', [
    'foreignKey' => 'product_id'
]);

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

SellerProductsTable.php

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

Now, In the view of categories (site.com/categories/view/2), I have to select all products data from products and sellerProducts where products belongs to category id and also exists in sellerProducts. ie.,

products.category_id = $id AND sellerProducts.product_id = Products.id

Is there any easy way in CakePHP 3 to get the result ?

Edit 2

This is what I'm trying. In view() action of CategoriesController.php

$this->loadModel('Products');
        $sellerProducts = $this->Products->find('all', [
          'conditions' => [
            'category_id' => $id
          ],
          'joins' => [
              'table' => 'SellerProducts',
              'alias' => 'SellerProducts',
              'type' => 'INNER',
              'conditions' => [
                'SellerProducts.product_id' => 'Products.id',
                'stock >' => 0
              ]
            ]
        ]);
        debug($sellerProducts);
        foreach($sellerProducts as $a) {
          debug($a);
        }

on debug it gives only the data from Products table but not from SellerProducts

Seems a very simple task, what didi you try so far? – arilia Aug 03 '16 at 08:26 — arilia, Aug 03 '16 at 08:26

arilia · Accepted Answer · 2016-08-03T11:29:19.907

4

instead of join you can simply contain SellerProducts

$this->Products->find()
    ->where(['category_id' => $id])
    ->contain(['SellerProducts' => function($q) {
        return $q->where(['stock >' => 0])
    }]);

if you want to query the data from sellerproducts you can do

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

edited Aug 03 '16 at 11:29

answered Aug 03 '16 at 11:09

arilia

9,373
2
20
44

this gives result in `2D` array in which 1st array contain `products` and 2nd array contains all `sellerProducts` associated with that product. I want all data in single array because I have to run many other functions later on the data retrieved like to find `min('selling_price')` and `max('selling_price')` which are in `sellerProducts`. and also to get only one seller with each product with minimum `selling_price`, etc. I think it will be easy to run these functions later on result of join. – Anuj TBE Aug 03 '16 at 11:25

CakePHP 3 : multi level association

1 Answers1