In cakePHP, how to retrieve joined result from multiple tables

Question

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

Answer 1

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

Answer 2

You should study HaBTM (Has and Belongs to Many) http://book.cakephp.org/view/78/Associations-Linking-Models-Together

Answer 3

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

Answer 4

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

Answer 5

$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