• How to use join for any relationship types in CakePHP
  • Scenario
  • Solution
  • The end

#How to use join for any relationship types in CakePHP

Generating reports from database is generally resource consuming process. When you need to combine multiple tables in order to form the report data, reducing database call becomes very important. In this tutorial, we talk about how to use join for multiple tables in CakePHP when association between models are not necessarily hasOne or belongsTo.

#Scenario

In CakPHP(we are referring to CakePHP 1.3 here) model, by default if the association between two models is hasOne or belongsTo, when we use find statement, CakePHP will automatically use left join sql statement to perform the database calls. However sometimes, we want to force CakePHP to perform join for models which are connected by hasMany, and we may sometimes want to join more than two tables together. Below is the solution.

#Solution

When using find() method, you can pass in option using 'joins' key to force a join statement.

$options=array(             
        'joins' =>
                  array(
                    array(
                        'table' => 'purchases',
                        'alias' => 'Purchase',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('Purchase.id = Coupon.purchase_id')
                    ),
                    array(
                        'table' => 'deals',
                        'alias' => 'Deal',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('Deal.id = Purchase.deal_id')
                    ), 
                     array(
                        'table' => 'merchants',
                        'alias' => 'Merchant',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('Merchant.id = Deal.merchant_id')
                    ),
                     array(
                        'table' => 'merchant_companies',
                        'alias' => 'MerchantCompany',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('MerchantCompany.id = Merchant.merchant_company_id')
                    )            
     )  
);
$coupons = $this->Coupon->find('all', $options);

The find() method above will use join on five tables to get the result, that is much efficient database call, comparing to fetch results using multiple sql statements.

One important note to take here, if you are using Containable behavior on this model, you will have to set recursive=-1 before you call find() method

?

$options=array(             
        'joins' =>
                  array(
                    array(
                        'table' => 'purchases',
                        'alias' => 'Purchase',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('Purchase.id = Coupon.purchase_id')
                    ),
                    array(
                        'table' => 'deals',
                        'alias' => 'Deal',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('Deal.id = Purchase.deal_id')
                    ), 
                     array(
                        'table' => 'merchants',
                        'alias' => 'Merchant',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('Merchant.id = Deal.merchant_id')
                    ),
                     array(
                        'table' => 'merchant_companies',
                        'alias' => 'MerchantCompany',
                        'type' => 'left',
                        'foreignKey' => false,
                        'conditions'=> array('MerchantCompany.id = Merchant.merchant_company_id')
                    )            
     )  
);
$this->recursive = -1;
$coupons = $this->Coupon->find('all', $options);

#The end

Hopefully this simple tip helped you with your development. If you like our post, please follow us on Twitter and help spread the word. We need your support to continue. If you have questions or find our mistakes in above tutorial, do leave a comment below to let us know