CODEWITHSUNDEEP
cakephpjoin
Manish Sharma
Manish Sharma

Reputation: 35

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

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

Upvotes: 15

Views: 71445

Answers (6)

Sanjun Dev
Sanjun Dev

Reputation: 518

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

Upvotes: 0

jedt
jedt

Reputation: 1691

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

Upvotes: -2

Sander Marechal
Sander Marechal

Reputation: 23216

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

Upvotes: 2

jmcneese
jmcneese

Reputation: 91

$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

Upvotes: 43

dr Hannibal Lecter
dr Hannibal Lecter

Reputation: 6721

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 :-)

Upvotes: 2

duckyflip
duckyflip

Reputation: 16499

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

Upvotes: 1

Related Questions