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

Answer 1

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.