[wp-hackers] Order posts by time of their last comment

[Mike Schinkel]

On Sep 28, 2010, at 10:25 AM, Christian Foster wrote:
> I am not sure of the consequence of ordering a large number of items
> but in the docs it down mention you can sort using a numerical value
> in a post meta key - orderby=meta_value_num. I haven't looked through
> the code as yet to see what difference it makes.

There's no difference in the way WordPress stores meta_values when "orderby=meta_value_num" vs. when "orderby=meta_value"; WordPress just adds a 0 to meta_value. In a nutshell it just does this:

SELECT post_id FROM wp_postmeta ORDER BY meta_value+0

Anyway I don't know why I didn't suggest just using SQL originally but your questions got me thinking to try a code pattern that I've been planning to try for a while which is create a class to extend WP_Query.  I posted the code as a Gist on Github here:

http://gist.github.com/601838

You'd call it something like this:

$query = new PostsByLatestCommentQuery('post_type=post');
echo "<ul>";
foreach($query->posts as $post) {
   echo "<li>{$post->comment_date} ({$post->comment_count}) -- {$post->post_title}</li>";
}
echo "</ul>";

Of course you will probably want to store the results in a transient because the query is still doing a lot of work and you might not want to run for every page load.

Hope this helps.

-Mike