Cakephp Join Multiple tables

Cakephp Join Multiple tables : Join is an important part for getting data collectively from two or more than two table. Every framework has its own rule for joining two or multiple tables. Syntax for join depends upon the framework you are using. Here in this tutorial we are going to explain the join with two and more than two table in cakephp framework. We will use cakephp model syntax to join the tables.

Cakephp Join Multiple tables

Suppose we have a model called User which is associated with user table. Here in this example we are going to join this table with profile table which has user_id as foreign key.

Cakephp join Two Tables Example-

   $userProfile = $this->User->find('all',
                array('joins' => array(
                                       array('table' => 'profile',
                                             'alias' => 'p',
                                             'type' => 'left',
                                             'foreignKey' => false,
                                             'conditions'=> array('user.id = p.user_id')                                             
                                        )
                                 ),
                 
                ));

The above example will join the two tables. If we go and see the query generated by this join in sql it will look something like this –

Cakephp join Three Tables Example-

Here is simple example of join three tables in cakephp.

  $joins[] =  array(
									'table' => 'post', 
									'alias' => 'post', 
									'type' => 'LEFT',
									'conditions' => array(
										'user.id = post.user_id'
									)
							);
			    $joins[] =  array(
									'table' => 'profile', 
									'alias' => 'profile', 
									'type' => 'LEFT',
									'conditions' => array(
										'user.id = profile.user_id'
									)
							);
										
				$userDetail = $this->User->find('all', array("fields" => array('user.*','post.*','profile.*')
                                                 "joins" => $joins
); 

The above example will join the three tables. The above example will select all fields of the tables which are joined. You can specify the fields which you want to select in fields. If we go and see the query generated by this join in sql it will look something like this –

Cakephp join select fields

You can select fields using the fields tag to specify the fields as in above example- array(“fields” => array(‘user.*’,’post.*’,’profile.*’)