How can i add additional conditions to the ON clause with Linkable?

0DownUp

I'm using the Linkable behavior (http://blog.rafaelbandeira3.com/2008/11/16/linkable-behavior-taking-it-easy-in-your-db/) to generate joined queries.

I have the following find operation:


        $episodes = $this->Show->Season->Episode->find('all', array(
            'link' => array(
                'Season' => array(
                    'Show' => array(
                        'ShowSubscription'
                    )
                ),
                'EpisodeEntry'
            ),
            'fields' => array('Episode.*', 'Season.*', 'Show.*', 'EpisodeEntry.*'),
            'conditions' => array(
                'ShowSubscription.user_id' => $this->Auth->user('id')
            ),
            'order' => array('Episode.gmtdate' => 'asc')
        ));

This results in the following SQL query:


SELECT `Episode`.*, `Season`.*, `Show`.*, `EpisodeEntry`.* FROM `episodes` AS `Episode` LEFT JOIN `seasons` AS `Season` ON (`Season`.`id` = `Episode`.`season_id`) LEFT JOIN `episode_entries` AS `EpisodeEntry` ON (`Episode`.`id` = `EpisodeEntry`.`episode_id`) LEFT JOIN `shows` AS `Show` ON (`Show`.`id` = `Season`.`show_id`) LEFT JOIN `show_subscriptions` AS `ShowSubscription` ON (`Show`.`id` = `ShowSubscription`.`show_id`) WHERE `ShowSubscription`.`user_id` = '2' ORDER BY `Episode`.`gmtdate` asc

How can i add an extra condition to the ON clause for the JOIN with EpisodeEntry?


LEFT JOIN `episode_entries` AS `EpisodeEntry` ON (`Episode`.`id` = `EpisodeEntry`.`episode_id`)

should become


LEFT JOIN `episode_entries` AS `EpisodeEntry` ON (`Episode`.`id` = `EpisodeEntry`.`episode_id` AND `EpisodeEntry`.`user_id` = '2')

Asked by Lemon, on 1/11/09

3 Answers

0DownUp

the link you posted is bust.. but seems like you need containable. its core cake code and think does the same as what you are looking for

Answered by dogmatic69on 1/11/09

0DownUp

With Containable (I don't know if it is the same API for Linkable) you can add a "conditions" key to reproduce the behavior you need.

Example from the doc:


$this->Post->find('all', array('contain' => array(
    'Comment' => array(
        'conditions' => array('Comment.author =' => "Daniel"),
        'order' => 'Comment.created DESC'
    )
)));

Answered by real34on 2/11/09

0DownUp

Ad7six wrote a "oneQuery" behavior on MIbase. It does what linkable do and actually much more (ie: optimization, formatting results ..)

Answered by francky06lon 5/11/09

<< previous next >>

Your Answer

You can use Creole Wiki Syntax to format your text.

Tagged with

Rating

0

Viewed

411 times

Last Activity

on 5/11/09

Related Questions