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

[Mike Schinkel]

On Sep 27, 2010, at 3:29 PM, Aaron Jorbin wrote:
> On 9/27/10, Christian Foster <christian.foster at gmail.com> wrote:
>> I have a requirement to list posts by the time of their last comment.
>> I am trying to keep it simple, the current page which lists by other
>> factors like post type uses a slightly modified pagination function
>> with a pre_get_posts filter to set the required query parameters, we
>> have tens of thousands of posts of different types so the pagination
>> is very important.
>> 
>> Can anyone think of a good way to sort a large number of posts by the
>> date of their last comment? Is it then possible to use the same
>> pagination function and posts_per_page/paged query variables?
> 
> I would add a post_meta field for the date/time of most recent comment
> and hook into comment_post and wp_set_comment_status to update it. You
> can then order by the meta value.

Personally I would recommend against ordering tens of thousands of records on a longtext value.  That's why I recommended using the term_order field of the wp_term_relationships table so he can order based on an efficient int rather than an inefficient longtext. 

Of course I probably should have mentioned that he might want to add an index on the term_order field to improve performance even more.

-Mike