cakephpinner-joinjoincakephp-3.2

join not working in CakePHP 3


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)

Solution

  • 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