Sort Posts (in the Loop) by Recent Comments or "Activity"

Okay, I just spent the last few hours trying to tackle the same task and found this thread along the way. I figured I’d leave my solution here in case you (or others) haven’t figured something else out.

The function below are methods within my plugin’s class. If you’d prefer to use functions in the global scope, adjust the function names and add_filter arguments accordingly.

In my constructor:

add_filter('query_vars', array(&$this, 'query_vars'));
add_filter('posts_join', array(&$this, 'join_last_comment'));
add_filter('posts_orderby', array(&$this, 'orderby_last_comment'));

Methods defined in my class:

function query_vars($vars) {
	$vars[] = 'orderby_last_comment';
	return $vars;
}

function join_last_comment($sql) {
	global $wpdb;
	if(get_query_var('orderby_last_comment')) {
		$sql .= '
			LEFT JOIN (SELECT comment_post_ID AS post, MAX(comment_date) AS date FROM '.$wpdb->comments.' WHERE comment_approved="1" GROUP BY post) AS last_comment
				ON last_comment.post = '.$wpdb->posts.'.ID
		';
	}
	return $sql;
}

function orderby_last_comment($sql) {
	if(get_query_var('orderby_last_comment')) {
		return 'last_comment.date DESC'.($sql ? ', '.$sql : '');
	}
	return $sql;
}

Usage within query_posts, WP_Query, etc.

query_posts('orderby_last_comment=1');
// or
query_posts(array('orderby_last_comment' => true));

Oh, that looks exactly like what I want! Have you just built this into your own plugin?

I tried putting your code into a page template, and then using query_posts('orderby_last_comment=1'); in a Loop, but that didn’t work. I got an unexpected $end when I opened the page.

Would I need to place the two sections of code in functions.php or somewhere other than the document I am using query_posts('orderby_last_comment=1');?

If you’re using a them within a template, they’ll need to go into your functions.php file, but you’ll need to rename the functions (at least query_vars) to avoid collision.

If you want, I could toss them into a small plugin file that you can include into your project.

It seems I’m getting a fatal error in the functions.php when I try to use any of the add_filter(); functions. It says it’s undefined. I’m new to this kind of PHP, so I’m not really sure what I’m doing wrong.

But yeah, I think you should include it into a plugin and make it public anyway. I know it would be pretty popular, and I know nothing quite like it exists. You’d be helping out tons of people like me who need this kind of functionality.

I have a plugin submission pending called Filter By Comments, but until it’s approved, I’ve uploaded the plugin to my own site. Let me know if everything works!

Hello

I need exactly this and and I’m desperate ??
I installed the plugin but I could not get it to work in index.php, can you give an example more explanatory with plugin + code at index.php?

Sorry for my bad english…

Up! Help!

Kevin, thanks for your contribution. This solution will sort strictly on comments, am I right? (I.e. if there is a new post without any comments, this post will sort after all posts with comments.)

Please direct all questions/comments regarding Filter By Comments to its plugin page.

This may help

[Code moderated as per the Forum Rules. Please use the pastebin]