Cakephp query data using join on multiple table or three different table join

This tutorial will provide you the instruction how you can query data join of three table or how you can query data from multiple table using the cakephp 2.3.0 . In the examples i have three tables with the name of the users, compaigns and payments. There is model class for the each table with the name of the User, Compaign and Payment. now i am going to fetch the data in the USER controller using the join over another two tables. Now about the table data there are unique users entries in the 'users' table but in the 'compaigns' table there are multiple entries associated with the each user and in the 'payments' table there are multiple entries of the payments associated with the different compaigns. So there is also a indirect relation of users with the payments. We are going to find out the users related amout's sum from the payments table. but the compaings creating the relation between the payments and users. After viewing the query you can develop you own or can modify this to develop your own Also i am going to share the original sql query that work same like the cakephp query code.

SQL QUERY:-

SELECT users.*, users.id AS uid, users.stagename, count(compaigns.id) as c_id, PYT.sumamount as total_amount FROM users
JOIN compaigns ON users.id = compaigns.sender_musician OR users.id = compaigns.user_id
LEFT JOIN ( select compaignId, sum(amount) as sumamount from payments group by compaignId ) as PYT ON compaigns.id = PYT.compaignId GROUP BY uid


CakePhp QUERY:-

Note: User model object used for build the query from the 'users' table but for the other two tables 'compaigns' and 'payments' not user the models object of those tables. So the direct table names of the the 'compaigns' and 'payment' are used direct in the join.

// these all are the conditions for the query for first table
$conditionArray['User.livePerformanceUrl !='] = '';
$conditionArray['User.musicUrl !='] = '';
$conditionArray['User.profile_image !='] = '';
$conditionArray['User.is_active'] = 1;

// join queries as joins parameter of the cake query
// both tables joins are done within this.
$joins = array(
array(
       'table' => 'compaigns',
       'alias' => 'compaigns',
       'foreignKey' => false,
       'type' => 'INNER',
       'conditions' => array( 'or' => array(
           array('User.id = compaigns.sender_musician'),
           array('User.id = compaigns.user_id'),
           )
       )
   ),
   array(
    'table' => 'payments',
'alias' => 'payments',  // the alias is 'included' in the 'table' field
   'type' => 'LEFT',
   'foreignKey' => false,
        'group' => array('payments.compaignId'), //fields to GROUP BY
   'conditions' => array(
       'compaigns.id = payments.compaignId'
   )
)
);

$fields_arr = array('User.id as uid, User.stagename, compaign.id as cid, sum(payments.amount) as total_amount');

// THIS IS THE FINAL QUERY
$musiciansList = $this->User->find('all',array('fields'=>$fields_arr,'joins'=> $joins, 'conditions' => $conditionArray,'limit'=>10,'group' => array('User.id'), 'order' => array('sumamount'=> 'DESC'),'recursive'=>-1));

In the variable $joins you can see i used the 'payments' table name instead the 'Payment' model object and same in this variable you can see the i used the 'compaigns' table name instead the 'Compaign' model class I mentioned this again just because of this most of beginners fail to build the join query. Enjoy this code and i'll back with more helpful stuff.