0

my problem is, that i want to count the votes for a comment of an article. So users can upvote good comments of an article and i want to list the comments with the most votes first. The approach that i'm following now is working, with the limitation, that only votes are listed, that already have been voted. those, that are not listed in the join table (comments_users) are ignored. to make it a bit more clear my tables are users, comments and the HABTM join table comments_users (alias votes)

my current approach is:

  public function commentsOfArticle($articleId){

    $options['group'] = array('Comment.articleId');
        $options['conditions'][] = array('Comment.article_id' => $articleId);

    $options['joins'][] = array('table' => 'comments_users',
        'alias' => 'Votes',
        'type' => 'inner',
        'conditions' => array(
            'Votes.comment_id = Comment.id'
        ));

    $options['fields'] = array('Comment.*','COUNT(Votes.user_id) AS votes');
    $options['contain'] = array(.......);
    $options[ 'order'] = array('votes DESC');


   return  $this->find('all',$options);
}

i think the key line is

  $options['fields'] = array('Comment.*','COUNT(Votes.user_id) AS votes');

is it possible to receive those comments, that have no entry in the votes table at the end of my results, just with votes=0 ?

tobysas
  • 308
  • 5
  • 18

1 Answers1

1

Try changing the JOIN from inner to left

$options['joins'][] = array('table' => 'comments_users',
    'alias' => 'Votes',
    'type' => 'LEFT',
    'conditions' => array(
        'Votes.comment_id = Comment.id'
    ));
AgRizzo
  • 5,261
  • 1
  • 13
  • 28