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

[Mike Schinkel]

On Sep 27, 2010, at 9:08 AM, Christian Foster 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?

Use the 'wp_insert_comment' hook to store the datetime value as an int in the term_order field of the wp_term_relationships table and . At worst it would be one minute off.  If you need, you can just create a dummy taxonomy and dummy term for this. Then it's a simple SQL join query to get the records sorted in the correct order.  Would that work?

-Mike