Creating left joins in cakephp

Question

I am trying to select data using LEFT join in cakephp, here how can i use more than two tables for LEFT join.Here iam creating LEFT join between two tables news_feeds and news_feed_likes.i want to introduce two more tables here, NewsFeedComments and newsfeed_likes_comments.How can i do this?

Thanks for your help

$this->paginate = array(
    'conditions' => array('NewsFeed.group_id'=>$groupdata['Group']['id'],'NewsFeed.status'=>'A'),
    'joins' => array(
        array(
            'alias' => 'newslikes',
            'table' => 'news_feed_likes',
            'type' => 'LEFT',
            'conditions' => array(
                'newslikes.news_feed_id = NewsFeed.id',
                'newslikes.user_id'=>$this->Auth->user('id'),
                'newslikes.status'=>'1',
            ),
        ),
        array(
            'alias' => 'newscommentslikes',
            'table' => 'news_feed_comments_likes',
            'type' => 'LEFT',
            'conditions' => array(
                'newscommentslikes.news_feed_comment_id = NewsFeedComment.id',
                'newscommentslikes.user_id'=>$this->Auth->user('id'),
                'newscommentslikes.status'=>'1',
            ),
        ),
    ),
    'fields' => array(
        'IFNULL(newslikes.user_id,0) AS likestatus', 
        'NewsFeed.id', 
        'NewsFeed.posted_message', 
        'NewsFeed.created', 
        'NewsFeed.user_id', 
        'NewsFeed.status', 
        'NewsFeed.newslike', 
        'IFNULL(newslikes.status,0) AS status',
     ),
    'order' => array('NewsFeed.created' => 'desc'),
);

$this->set('newsfeed', $this->paginate( $this->NewsFeed ) );

'joins' => array(array(..first join..), array(..second join..), ..., array(.. nth join..)); Have you tried just adding more arrays to the joins array? — iso27002
i have checked this please check my code above, it's giving me error Unknown column 'NewsFeedComment.id' in 'on clause', here how can i use another model NewsFeedComment. — user1851420
Basically it should return 0 if there is no newscommentslikes for news_feed_comments — user1851420

Chuck Burgess Chuck Burgess · Accepted Answer · 2013-12-18T07:02:54

My first bit of advice would be to consider moving this to the the model. However, to answer the specific question, just add more joins based on the foreign key. So, for example, if you have a foreign key between NewsFeedLike and NewsFeedLikeComments, you can just add another join like so:

'joins' => array(
    array(
        'table' => 'news_feed_like_comments',
        'alias' => 'NewsFeedLikeComment',
        'type' => 'LEFT',
        'conditions' => array(
            'NewsFeedLikeComment.news_feed_like_id = NewsFeedLike.id',
        ),
    ),

Because you are already joining NewsFeedLike, it will use those conditions to limit the NewsFeedLikeComment.

Another tip: Always follow the CakePHP coding standards when writing your code. It will make your life a lot less painful. Avoid using lowercase underscored aliases for your models. Always write them CamelCase with a capital first letter and singular. So in your joins, instead of newslikes and newscommentslikes as aliases, you should use NewsLike and NewsCommentLike.

Creating left joins in cakephp

2 Answers