15

can anyone tell me, how to retrieve joined result from multiple tables in cakePHP ( using cakePHP mvc architecture). For example, I have three tables to join (tbl_topics, tbl_items, tbl_votes. Their relationship is defined as following: a topic can have many items and an item can have many votes. Now I want to retrieve a list of topics with the count of all votes on all items for each topic. The SQL query for this is written below:

SELECT Topic.*, count(Vote.id) voteCount FROM tbl_topics AS Topic LEFT OUTER JOIN tbl_items AS Item ON (Topic.id = Item.topic_id) LEFT OUTER JOIN tbl_votes AS Vote ON (Item.id = Vote.item_id);

My problem is I can do it easily using $this-><Model Name>->query function, but this requires sql code to be written in the controller which I don't want. I'm trying to find out any other way to do this (like find()).

Improve this question

6 Answers 6

Reset to default
43 
$markers = $this->Marker->find('all', array('joins' => array( array( 'table' => 'markers_tags', 'alias' => 'MarkersTag', 'type' => 'inner', 'foreignKey' => false, 'conditions'=> array('MarkersTag.marker_id = Marker.id') ), array( 'table' => 'tags', 'alias' => 'Tag', 'type' => 'inner', 'foreignKey' => false, 'conditions'=> array( 'Tag.id = MarkersTag.tag_id', 'Tag.tag' => explode(' ', $this->params['url']['q']) ) ) )));

as referred to in nate abele's article: link text

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

for clarity sake, the example is just to show how to manually add joins to a model::find's parameters.
Your above find function gets only fields in the Marker table not the other two tables.
@NaveenKumar : You have to mention fields in another array e.g $this->Marker->find('all', array('joins' => array(.....), 'fields' => array(MarkersTag.marker_id,Marker.id));
2

I'll be honest here and say that you'll probably be a lot happier if you just create a function in your model, something like getTopicVotes() and calling query() there. Every other solution I can think of will only make it more complicated and therefore uglier.

Edit:

Depending on the size of your data, and assuming you've set up your model relations properly (Topic hasMany Items hasMany Votes), you could do a simple find('all') containing all the items and votes, and then do something like this:

foreach ($this->data as &$topic) { $votes = Set::extract('/Topic/Item/Vote', $topic); $topic['Topic']['vote_count'] = count($votes); }

Two things are important here:

  1. If you have a lot of data, you should probably forget about this approach, it will be slow as hell.
  2. I've written this from my memory and it might not look like this in real life and/or it may not work at all :-)
Improve this answer

Comments

2

You can easily set the "recursive" property on a find() query.

$result = $this->Topic->find('all', array('recursive' => 2));

Alternatively, you can use the Containable behavior in your model. Then you can use:

$this->Topic->contain(array( 'Item', 'Item.Vote', )); $result = $this->Topic->find('all');

or

$result = $this->Topic->find('all', array( 'contain' => array( 'Item', 'Item.Vote', ), ));
Improve this answer

2 Comments

I tried using contain as you described and it doesn't work. Also, I would advise against using recursive = 2, because, if you have many associations, you clog up your app.
@Alex: There was a bug in my example. I fixed it. Be sure to add the Containable behaviour to the model!
1

What you need is recursive associations support, which is not possible with stock CakePHP currently.
Although it could be achieved using some bindModel trickery
or an experimental RecursiveAssociationBehavior.

Both of these solutions will either require you to use extra code or rely on a behaviour in your application but if you resist the temptation to write pure SQL code, you'll be rewarded with being able to use Cake`s pagination, auto conditions, model magic etc..

Improve this answer

Comments

0

I think this answer is already submitted, but I am posting here for someone who seeks still for this. The joins can be done with find() method can be like below

$result = $this->ModelName1->find("all",array( 'fields' => array('ModelName1.field_name','Table2.field_names'), // retrieving fileds 'joins' => array( // join array array( 'table' => 'table_name', 'alias' => 'Table2', 'type' => 'inner', 'foreignKey' => false, 'conditions'=> array('ModelName1.id = Table2.id') // joins conditions array ), array( 'table' => 'table_name3', 'alias' => 'Table3', 'type' => 'inner', 'foreignKey' => false, 'conditions'=> array('Table3.id = Table2.id') ) )));
Improve this answer

Comments

-2

You should study HaBTM (Has and Belongs to Many) http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

Improve this answer

3 Comments

I don't get it, how does HABTM apply here?
Try using class Item extends AppModel { var $belongsTo = array('Topic') }
Isn't habtm for two tables, where the question is asking about joining three?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.