alanft

just in case anyone ever googles back to this, here’s what i’m trialling at the mo, and it’s working ok, though not strongly tested…

the code below in my functions.php gives an ordered by recent comment date with 25 results per page when you add ?bboard to the URL of the home page or an archive page.

if (isset($_GET['bboard']))
{	add_filter('posts_fields', 'bboard_posts_fields');
	add_filter('posts_join_request', 'bboard_posts_join_paged');
	add_filter('posts_groupby', 'bboard_posts_groupby');
	add_filter('posts_orderby', 'bboard_posts_orderby');
	add_filter('post_limits', 'bboard_post_limits');
}

function bboard_posts_fields($fields)
{	return $fields.", MAX(comment_date) as max_comment_date, max(comment_ID) as latest_comment_id"; }

function bboard_posts_join_paged($join)
{	global $wpdb;
	return $join." INNER JOIN wp_comments ON ( $wpdb->posts.ID = $wpdb->comments.comment_post_ID AND comment_approved=1) ";
}

function bboard_posts_groupby($groupby)
{	if ($groupby=="") $groupby.=" wp_posts.ID";
	return $groupby;
}

function bboard_posts_orderby($orderby)
{	return "max_comment_date desc"; }

function bboard_post_limits($limits)
{	global $wp_query;
	$q=$wp_query->query_vars;
	if ($q['paged'] && $q['paged']>1) $offset = "offset ".(25*($q['paged']-1));
	return "LIMIT 25 $offset";
}

within the loop, to get the content of that latest comment use

$comment=get_comment($post->latest_comment_id);

and then you can use standard comment tags like comment_excerpt() and get_comment_date() as normal

thanks. i checked that c2c plugin and i realise on re-reading my initial post that i was being far from clear.

what i am after is something that will change the standard Loop to show posts in a ‘most recent comment’ order rather than the usual blog standard order of ‘most recently posted’.