Sort comments by votes?

Question

I am creating an application similar to stackoverflow in which it has ratings for questions and answers, and I also have tabs that show comments on the oldest, newest and voices. I have problems sorting by voice.

Here is my function:

 /**
     *
     * @param int $threadid
     * @param string $tab
     * @param object $voting referencing Voting.class.php (may not be needed)
     * @return database query/array 
     */
    public function getComments($threadid, $tab = 'oldest', $voting = null) {
        if ($tab == 'oldest') {
            $sql = "SELECT * FROM comments WHERE threadid = :threadid ORDER BY date ASC";
        } else if ($tab == 'newest') {
            $sql = "SELECT * FROM comments WHERE threadid = :threadid ORDER BY date DESC";
        } else if ($tab == 'votes') {
            //i dont know what to do here? read below for more explanation
        } else {
            $sql = "SELECT * FROM comments WHERE threadid = :threadid ORDER BY date ASC";
        }

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':threadid', $threadid);
        $stmt->execute();
        $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $row;
    }

Here is my database design:

**comments:** | id | userid | threadid | message | date |

**commentsrating:** | userid | commentid | voteup | votedown |

If the ratings are in a separate table from the comments, is it possible to make a request for $tab == 'votes'which will match the rest of the code?

and finally HTML:

<?php //Get comments
if (isset($_GET['tab'])) {
    $getComments = $thread->getComments($threadid, $_GET['tab'], $voting);
} else {
    $getComments = $thread->getComments($threadid);
} ?>

    <?php for ($i = 0; $i < count($getComments); $i++) { ?>
               <p>
    <?php echo $getComments[$i]['message']; ?>
               </p>
               <p>  
                   <span class="bid_votes_count" id="bid_votes_count<?php echo $getComments[$i]['id'] ?>">
    <?php echo $voting->getEffectiveCommentVotes($getComments[$i]['id']) . " votes"; ?>
                   </span>

                   <span class="bid_vote_buttons" id="bid_vote_buttons<?php echo $getComments[$i]['id'] ?>">
                      <a href="javascript:;" class="bid_vote_up" id="<?php echo $getComments[$i]['id'] ?>"></a>
                      <a href="javascript:;" class="bid_vote_down" id="<?php echo $getComments[$i]['id'] ?>"></a>
                  </span>
              </p>
    <?php } ?>

Thanks in advance!

Answer 1

Add ratingto the table commentsand save it manually or using a trigger in the table commentsrating.

, , .

Answer 2

:

$sql = "SELECT * FROM comments c JOIN (SELECT commentid, ". 
       "(SUM(voteup) - SUM(votedown)) votes, " . 
       "FROM commentsrating GROUP BY commentid) i ON " .
       "c.id = i.commentid WHERE c.threadid = :threadid " . 
       "ORDER BY i.votes DESC";

, JOIN, "", .

Answer 3

- ( ). , , .

: | userid | | |

The reason you should have a vote is because the comment rating can only have one vote. This cannot be both before and without a vote.

vote must be int (1) +/-. eg. It can be 1 or -1 (or 0 theoretically). This way you can do the following SQL:

SELECT c.id, c.message, c.date, SUM(cr.vote) AS 'votes'
FROM `comments` c
JOIN `commentsrating` cr ON c.id=cr.commentid
WHERE c.threadid=':threadid'
GROUP BY c.id
ORDER BY `votes`

The request will take longer than the order bytotal_votes field , but this should be done anyway.

Answer 4

SELECT * FROM `comments` WHERE `threadid`=:threadid ORDER BY (SELECT `voteup`-`votedown` FROM `commentsrating` WHERE `commentid`=`comments`.`id`) DESC

Give it a try?