join not working in CakePHP 3

Question

I'm using this code in controller to get joined result instead of contain associated in CakePHP 3.2

$abc = $this->Products->SellerProducts->find('all', [
          'conditions' => [
            'Products.subcategory_id' => $id,
            'SellerProducts.stock >' => 0,
          ],
          'join' => [
            'products' => [
              'table' => 'Products',
              'type' => 'INNER',
              'conditions' => [
                'products.id = SellerProducts.product_id'
              ]
            ],
            'brands' => [
              'table' => 'Brands',
              'type' => 'INNER',
              'conditions' => 'brands.id = products.brand_id'
            ],
            'product_colors' => [
              'table' => 'ProductColors',
              'type' => 'INNER',
              'conditions' => 'product_colors.product_id = products.id'
            ],
            'colors' => [
              'table' => 'Colors',
              'type' => 'INNER',
              'conditions' => 'colors.id = product_colors.color_id'
            ]
          ]
        ]);

But on debug, it gives only data from SellerProducts and is not including (joining) other tables.

I want to get joined result and not associated result using contain method because contain gives multilevel array which is difficult to get collection of associated data from multilevel array.

Edit 2 : Result of debug($abc);

SELECT SellerProducts.id AS `SellerProducts__id`, SellerProducts.seller_id AS `SellerProducts__seller_id`, SellerProducts.product_id AS `SellerProducts__product_id`, SellerProducts.selling_price AS `SellerProducts__selling_price` FROM seller_products SellerProducts INNER JOIN Products products ON products.id = SellerProducts.product_id INNER JOIN Brands brands ON brands.id = products.brand_id INNER JOIN ProductColors product_colors ON product_colors.product_id = products.id INNER JOIN Colors colors ON colors.id = product_colors.color_id WHERE (Products.subcategory_id = :c0 AND SellerProducts.stock > :c1)

See edit 2, it joins but selecting only sellerProducts columns — Anuj TBE, Aug 04 '16 at 11:20
specify the fields to fetch other columns. `SellerProducts->find('all', array('fields' => array()...)` — Riad, Aug 04 '16 at 11:23
Thanks, but what if I want to select all fields from few tables? — Anuj TBE, Aug 04 '16 at 11:39
For most (though not all) queries, you can use `contain` instead of `join` to do the same thing with simpler syntax. You might also look at `autoFields`. — Greg Schmidt, Aug 05 '16 at 03:22

Riad · Accepted Answer · 2016-08-04T15:15:44.360

0

I think you may write like this for all fields:

$this->SellerProducts->find('all',['fields' => [], 'conditions' => []]

OR

$this->SellerProducts->find('all', [
      'conditions' => [
        'Products.subcategory_id' => $id,
        'SellerProducts.stock >' => 0,
      ]])->join(.....);

See Cakephp 3 join

edited Aug 04 '16 at 15:15

answered Aug 04 '16 at 11:46

Riad

3,822
5
28
39

score 0 · Answer 2 · answered Dec 12 '16 at 06:24

Try this

 $SellerProducts = TableRegistry::get('SellerProducts');
 $resultSet = $SellerProducts->find('all')->hydrate(false)
                    ->select(['SellerProducts.id'])                           
                    ->join([
                        'Products'=>  [
                            'table'      => 'products',
                            'type'       => 'INNER',
                            'conditions' => 'Products.id = SellerProducts.product_id',
                        ],
                        'Brands'=>  [
                            'table'      => 'brands',
                            'type'       => 'INNER',
                            'conditions' => 'Brands.id = Products.brand_id',
                        ],
                        'ProductColors'=>  [
                            'table'      => 'product_colors',
                            'type'       => 'INNER',
                            'conditions' => 'ProductColors.product_id = Products.id',
                        ],
                        'Colors'=>  [
                            'table'      => 'colors',
                            'type'       => 'INNER',
                            'conditions' => 'Colors.id = ProductColors.color_id',
                        ]
                    ])
                    ->where([
                        'Products.subcategory_id' => $id,
                        'SellerProducts.stock >'  => 0
                    ])
                    ->toArray();
    print_r($resultSet);

join not working in CakePHP 3

2 Answers2