Home » CakePHP, PHP/MySQL » Joining multiple tables in CakePHP using bindModel method

4

One of the most powerful features of CakePHP is the ability to link relational mapping provided by the model. In CakePHP, the links between models are handled through associations. The associations are defined in the models as hasOne, hasMany, belongsTo etc. It is possible to define deep relationships involving many tables and retrieve data. But CakePHP doesn’t always form a single query to associate many tables and retrieve records. Often it joins a couple of tables first and look in the other tables matching the IDs of the first query result. So a multiple join association is achieved through more than one queries. This method often troubles you when you want to do a search in fields across multiple tables.

Let me explain this concept of multi-level join using an example. This example might not make sense to many. This is just to give you an idea about the concept i used to join multiple tables in a single query in cakephp. Through this example I am going to present you an idea on  joining 3 related tables. Also, the query generated by CakePHP is one and note more than one. This tutorial will also give you ideas on Creating and Destroying Associations on the fly, Model Associations in CakePHP & Nested model accessing.

Ok. This is my schema. I have three related tables.

Table Relationship

Table Relationship

‘Categories’ has many ‘Topics’
‘Topics’ has many ‘Reviews’

The association is defined in the Topics model. The model ‘topics.php‘ looks like the below.

<?php
class Topic extends AppModel {
    var $name = 'Topic';
	var $belongsTo = array(
		'Category' => array(
			'className'    	=> 'Category',
			'foreignKey'    => 'category_id'
		)
	);
	var $hasMany = array(
		'Review' => array(
			'className'    	=> 'Review',
			'foreignKey'    => 'topic_id'
		)
	);
}
?>

Here is my Topics Controller:

<?php
class TopicsController extends AppController {
	var $name = 'Topics';
	function index() {
		debug($this->Topic->find('all'));
	}
}
?>

So when I do a FIND ALL on Topic, I expect the associated records to be fetched from the Category and Review tables.
Yes. This happens without any problem. I got this on my screen.

Array
(
    [0] => Array
        (
            [Topic] => Array
                (
                    [id] => 1
                    [category_id] => 1
                    [title] => OOPS in PHP
                    [content] => OOPS!
                    [status] => 1
                )
            [Category] => Array
                (
                    [id] => 1
                    [name] => PHP
                    [status] => 1
                )
            [Review] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [topic_id] => 1
                            [review] => This article on OOP is really good.
                        )
                )
        )
)

But, when I looked at the generated Query in the Debug Console I see 2 queries like this.

1) SELECT `Topic`.`id`, `Topic`.`category_id`, `Topic`.`title`, `Topic`.`content`, `Topic`.`status`, `Category`.`id`, `Category`.`name`, `Category`.`status` FROM `topics` AS `Topic` LEFT JOIN `categories` AS `Category` ON (`Topic`.`category_id` = `Category`.`id`) WHERE 1 = 1

2) SELECT `Review`.`id`, `Review`.`topic_id`, `Review`.`review` FROM `reviews` AS `Review` WHERE `Review`.`topic_id` IN (1, 2, 3)

So CakePHP joined the Categories and Topics tables first. The items from Reviews table are fetched in a separate query using the IN() method.

The problem now is I won’t be able to add conditions to my FIND ALL query where I would want to filter some records in Review table. If I modify my Find function to this:

debug($this->Topic->find(‘all’, array(‘conditions’=>’Review.id=1′)));

This will throw an error because the Review table is joined in the first query.

This is when we have to think about a different method so all the 3 tables are joined in a single query as we normally do in manual SQL queries. We can write manual SQL queries in CakePHP but you won’t get the advantage of Pagination, Sort features with your custom query.

A pretty simple solution now would be to use bindModel method. There is  a trick. Even in this method, when we use hasMany association, CakePHP tends to generate multiple queries. So here, we will use a belongsTo relationship ladder defined in the controller on the fly using the bindModel method.

Now see how my Topics Controller looks like:

<?php
class TopicsController extends AppController {
	var $name = 'Topics';
	function index() {
		$this->Topic->Review->bindModel(array(
			'belongsTo' => array(
				'Topic' => array('foreignKey' => false,
									'type'=>'RIGHT',
									'conditions' => array('Review.topic_id = Topic.id')
								),
				'Category' => array(
									'foreignKey' => false,
									'type'=>'RIGHT',
									'conditions' => array('Topic.category_id = Category.id')
								)
							)
				),
			false
		);
		debug($this->Topic->Review->find('all'));
	}
}
?>

Notice that I have specified the join to be “RIGHT JOIN” so that I would be able to fetch all the records even if there are no reviews for a topic. You might want to use a “LEFT JOIN” for the Topic->Category relationship.

CakePHP now ran only one query to fetch records from all the 3 tables allowing me to specify conditions in the query relating to any of the 3 tables.

Confusing? Please let me know if you have any questions.

4 Comments

  1. mlay says:

    Why just dont make a database-view!?
    Its an easy way to get all the attributes you want to print out in a CakePHP-View. ContainingBehaviour and binding-trickery ist just inconvenient in my oppinion.

    What do you think about that, teknoid?

  2. Andreas says:

    Yes I got a million questions.

    After struggling about 3 hours to transfer this successfully to my project (and failed), I got the following remarks. Please correct me where I didnt get it right.

    I think that this way we end up using Review to access Topic. Why not just load the Review model and use that right away?

    Also If I understood well, I need to redefine every association of Topic I need to use in the query, which kind of defeats the purpose of associations doesnt it? Cause the idea behind this query is fundamental and amazingly common, so that will have to happen a lot.

    Finally, what happens if Category BelongsTo Group and Group belongsTo webSite and so on. Can I define these associations in this bindModel or I need to write another one, or nest them or something.

    In any case thx for the tutorial. It can prove useful in some cases, but I dont think it can be the standard way to query multiple tables.

  3. newbiecakephp says:

    authors
    book_authors
    books
    book_subjects
    subjects

    What if you have these tables? how can you connect authors-books-subjects

  4. karan shah says:

    dear your example is too good but i want to create a cascade drop down list
    in cake php will you please help me

    my problem
    whent i select countrie according to that my state should bind when i select state my district should bind…

Leave a Reply

Page optimized by WP Minify WordPress Plugin