计算一个表并按ORDER BY计算

Question

I want to order my blog posts by the amount of comments. To retrieve my posts etc i use:

$result = DB::query("SELECT R.rumorID, R.rumor_author, R.rumor_title, R.rumor_text, T.topic_name, A.first_name, A.surname, T.topicID, R.datum_geplaatst
                    FROM Rumor R JOIN Account A
                    ON A.userID = R.rumor_author
                    JOIN Topic T
                    ON T.topicID = R.topicID
                    ORDER BY $sortby $order");


        foreach ($result as $rumor) {
            $r_id = $rumor['rumorID'];

The $sortby and $order is a $_GET, i use links to "order by" the blog.

To retrieve the number of comments that comes with a blog post, i use:

$commentcount = DB::query("SELECT count(*)
                           FROM Rumor R JOIN Comment C
                           ON R.rumorID = C.rumorID
                           WHERE R.rumorID = :rumor", array(':rumor' => $r_id));

            $rumortext = $rumor['rumor_text'];
            $date = $rumor['datum_geplaatst'];
            $countcomments = $commentcount[0]['count(*)'];

So, I can order by Date,title, topic, author etc. But how do i manage to order by the amount of comments?

Answer 1

This could be way off. Writing from the top of my head here:

SELECT R.rumorID, R.rumor_author, R.rumor_title, R.rumor_text, T.topic_name, A.first_name, A.surname, T.topicID, R.datum_geplaatst, count(c.rumorID) AS commentCount 
FROM Rumor R 
JOIN Account A ON A.userID = R.rumor_author 
JOIN Topic T ON T.topicID = R.topicID 
LEFT JOIN Comment C ON R.rumorID = C.rumorID 
GROUP BY R.rumorID 
ORDER BY commentCount ASC