嵌套选择制作流行度算法

Question

I'm trying to write a sql function for making a popularity algorithm.

I'd like to sort "posts" by the amount of comments in "comments" with the "post_id" of the post, added to the amount of votes in the "votes" table with the "post_id" of the post, all sorted by "date" in the "posts" table (a timestamp).

I'm not sure how to go about doing this multi-table sorting, so i'm reaching out for some guidance!

Thanks in advance, Will.

Answer 1

I haven't tested but it may work:

SELECT `post_id`, COUNT(`post_id`) AS `count` FROM `comments` GROUP BY `post_id`

And additionally you can join it with the posts table:

SELECT * FROM `posts` LEFT JOIN (SELECT `post_id`, COUNT(`post_id`) AS `count` FROM `comments` GROUP BY `post_id`) AS `p` ON(`post_id`)