How do I write a join query across multiple tables in CakePHP?

Question

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()).

score 42 · Answer 1 · answered May 10 '09 at 15:20

42

$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

answered May 10 '09 at 15:20

jmcneese

91
1
3

for clarity sake, the example is just to show how to manually add joins to a model::find's parameters. – jmcneese May 10 '09 at 15:21
3

Your above find function gets only fields in the Marker table not the other two tables. – Naveen Kumar Mar 06 '12 at 09:50
3

@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)); – shivshankar Aug 08 '12 at 11:54

dr Hannibal Lecter · Answer 2 · 2009-04-30T23:53:24.997

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:

If you have a lot of data, you should probably forget about this approach, it will be slow as hell.
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 :-)

Sander Marechal · Answer 3 · 2010-04-07T22:28:22.543

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',
    ),
));

edited Apr 07 '10 at 22:28

answered May 09 '09 at 10:01

Sander Marechal

22,978
13
65
96

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 Ciminian Mar 29 '10 at 10:06
@Alex: There was a bug in my example. I fixed it. Be sure to add the Containable behaviour to the model! – Sander Marechal Apr 07 '10 at 22:29

score 1 · Answer 4 · answered Apr 30 '09 at 23:04

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..

score 0 · Answer 5 · answered May 29 '19 at 09:15

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')
    )
)));

score -2 · Answer 6 · edited Mar 03 '14 at 06:57

-2

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

edited Mar 03 '14 at 06:57

Sadikhasan

18,365
21
80
122

answered Apr 30 '09 at 14:24

jedt

1,691
1
17
20

I don't get it, how does HABTM apply here? – dr Hannibal Lecter Apr 30 '09 at 15:57
Try using class Item extends AppModel { var $belongsTo = array('Topic') } – jedt May 01 '09 at 02:00
Isn't habtm for two tables, where the question is asking about joining three? – SeanDowney Sep 02 '09 at 23:45

How do I write a join query across multiple tables in CakePHP?

6 Answers6

Linked

Related