Sort posts by activity date

I like the idea proposed in this comment. Connecting the posts table to the comments table seems like it’d be some super complicated SQL (well all SQL is complicated to me). Instead the link proposes that you add a post meta field to the post to hold the date of the most recent comment.

add_action('comment_unapproved_to_approved', 'wpa_144482_comment_approved');

function wpa_144482_comment_approved($comment) {
    $comment_post_ID = $comment->comment_post_ID;
    $date = $comment->comment_date;
    update_post_meta( $comment_post_ID, '_recent_comment_date', $date );
}

Then you should be able to sort your posts by the _recent_comment_date field.

There is also the Filter by Comments plugin but it hasn’t been updated in a long while so I couldn’t vouch for it.