How to display posts sorted by latest comments in WordPress

WordPress (WP) has a universal function called `get_posts`, but unfortunately, it doesn’t work in 100% of cases. Sometimes, to get the desired result, you need to manually build an SQL query. I don’t really like doing that, since I don’t know all the ins and outs of WP’s internal mechanisms (such as where and when certain filters should be applied). But when the task requires it, you have to write custom SQL.

This time, I needed to display a list of posts sorted in such a way that the post with the most recently added and approved comment appears first. In descending order — fresher ones at the top, older ones below.

To achieve this, we write the following SQL query and PHP code:

global $wpdb;

$sql = "SELECT `p`.ID, `cl`.`comment_date_last`
FROM {$wpdb -> posts} `p`
INNER JOIN (
	SELECT `comment_post_ID`, MAX(comment_date) `comment_date_last`
	FROM {$wpdb -> comments} `c`
	WHERE `comment_approved` = 1
	GROUP BY `comment_post_ID`
) `cl`
ON (`cl`.`comment_post_ID`=`p`.`ID`)
WHERE `p`.post_type = 'post'
AND `p`.post_status = 'publish'
ORDER BY `comment_date_last` DESC";
$posts_ar = $wpdb -> get_results($sql, ARRAY_A);

foreach($posts_ar as $post_ar):
	echo $post_ar['ID'];
	/* … */
endforeach;

The first thing we need to do is call the global WordPress database object — `$wpdb`.

Then comes the query, which consists of two parts:

1. The main query that selects data from the posts table.
2. A subquery with an `INNER JOIN` constraint, which allows us to include only those posts that have comments. In the subquery, we select the most recent comment for each post using the `MAX()` function. To connect the subquery with the main one, we include the `comment_post_ID` field in the selection.

Using `WHERE`, we limit the selection in the main query to posts only (`p`.post_type = 'post') and with a `publish` status (`p`.post_status = 'publish'). And, of course, we sort everything by comment date: `ORDER BY comment_date_last DESC`.

Finally, we display the post list using a PHP `foreach` loop:

foreach($posts_ar as $post_ar):
	echo $post_ar['ID'];
	/* … */
endforeach;

In the code above, we only output the post ID. But it can be passed to various WP functions such as `get_the_title()`, `get_permalink()`, and many others.