Writing a query ordered by the number of associations between objects

Question

It's time to implement sorting on my blog-like web application. In addition to browsing by creation date, I want users to be able to sort by the number of replies, too.

Given that I have two tables blog_posts and replies, and that replies has a field called blog_post_id (a foreign key for the blog_post it's a reply to), how do I write this query?

"Select the 30 blog posts with the most replies, sorted by the number of replies for each blog post in descending order."

I also want to paginate this. Will it be hard to get the next 30, the next 30, etc.?

My feeling is that this isn't too hard to do with SQL, I just don't know how to do it (still learning).

Answer 1

I don't have a copy of MySQL locally that I can mess around with, but you could do something like this:

SELECT
blog_post.id,
[other relevant fields from blog_post],
(SELECT COUNT(*) FROM replies WHERE blog_post_id = blog_post.id) 'replies'
FROM
blog_post
ORDER BY FIELD('replies')

Not sure if the order by field will work with a sub query, but it's worth a shot.

Good luck!

Ian

Answer 2

This should do the trick:

SELECT blog_posts.*, count(replies.blog_post_id) as blog_replies 
FROM blog_posts 
LEFT JOIN replies ON replies.blog_post_id = blog_posts.id 
GROUP BY blog_posts.id 
ORDER BY blog_replies DESC

You could tack on a LIMIT and OFFSET clause at the end to get the paging working.

Answer 3

Something like:

select
    replies = (select count(*) from replies where blog_post_id = b.id),
    id,
    ...
from blog_post b
order by 1 desc
limit 30;