New Blog Functionality: Recent Commentary
I’ve had a Recent Comments section on the front-page sidebar of the site for quite some time, and decided it was time for an upgrade. The old version was straightforward: it displayed a list of the most recent comments and who made them. What I decided I wanted was, rather, a list of the posts with the most recent comments on them, how many comments there were, and a list of the people who made the comments in timestamp order.
That’s what I have on the front page now. I wrote this functionality as a WordPress plugin, and will release it sometime soon—I’m not doing so yet because to work as expected it needs the user to write some CSS, so either I write docs on how to do that, or I add an option to insert some styling with the widget HTML.
I think it’s a better approach, because it displays more information in about the same area, and because it allows posts with discussions to remain present in the widget for longer, rather than being bumped off due to multiple comments about some single post. I think that’s likely to be more interesting.
At the heart of the plugin are two SQL queries that I couldn’t figure out how to turn into a single query, and which took me a little while to figure out.
The desired result is a list of the n posts with the most recent comments on them, and a list of the comments for each post, with the comment list linked to the post in some fashion. I initially thought that the first query would be easy, but I was making poor assumptions about SELECT DISTINCT that mean that I was actually getting a list of the posts with comments ordered by the time of the first comment, not the latest comment. Simplified somewhat, the right way to do that is this:
SELECT wp_posts.ID, MAX(wp_comments.comment_date) as datesort
FROM comments
LEFT JOIN wp_posts ON (wp_comments.comment_post_ID = wp_posts.ID)
WHERE wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'post'
AND wp_comments.comment_approved = 1
GROUP BY wp_posts.ID
ORDER BY datesort DESC
If, as I did initially, you use SELECT DISTINCT, or you use GROUP BY without the MAX function and sort by comment_date instead of datesort, it’s sorted by the first comment, not the latest comment.
From those results we have the latest posts. After putting their IDs into a comma-separated list, we can then get the comments we want:
SELECT *
FROM wp_posts
LEFT JOIN wp_comments ON (wp_posts.ID = wp_comments.comment_post_ID)
WHERE wp_posts.ID IN ($comma_separated_list_of_post_ids)
AND wp_comments.comment_approved = 1
ORDER BY field(wp_posts.ID, $comma_separated_list_of_post_ids), wp_comments.comment_date;
This basically means “go get the comments on the specified posts”. The only interesting part is the ordering: I wanted to get the rows back so that the comments about a given post would all be together, and it turns out that the field() function is the way to do this, as it lets you sort things by whatever arbitrary order you like: it takes the column to sort on as the first argument, and a comma-separated list of values as the arbitrary order.
(It turns out that I didn’t need the ordering from field(), because of the PHP that later handles this data, but I left it in the example because it’s an interesting feature.)
After I have the results from that query, I go through them in PHP to organize into objects that I can handle more easily, essentially placing the post data at the outermost level and the comment data corresponding to each post inside the post data ($post_ids is the array of IDs of the posts with recent comments, $recent_commentary_results is the result from the latter SQL query above):
$recent_commentary = array();
foreach ($post_ids as $key=>$value) {
//Go through each post, extract the data relevant to it from the
//query results, and add it to the array we're going to return.
$postdata = array(
"id"=>$value,
"comments"=>array()
);
foreach ($recent_commentary_results as &$row) {
//If the row matches the current post, insert the title, the
//comment count, and add the row itself to the list of comments
//on that post.
if ($value == $row->ID) {
$postdata["title"] = $row->post_title;
$postdata["comment_count"] = $row->comment_count;
array_push($postdata["comments"], $row);
}
}
array_push($recent_commentary, $postdata);
}
return $recent_commentary;
That returns the required information in a format that’s relatively easy to go through and turn into HTML; none of the rest of the plugin code is particularly interesting.
I hope to release the plugin later this week.