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

[Jeremy Clarke]

On Mon, Sep 27, 2010 at 9:08 AM, Christian Foster <
christian.foster at gmail.com> wrote:

>
> 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?
>
>
My solution to this problem involves an extra query but simplifies the rest
of the problem out of existence.

Just do a get_comments() query with a relatively high posts_per_page (or
whatever it is for comments) to get the most recent comments, then use their
parent data to figure out the posts they are on. Those are the most recently
commented posts.

This works great as both queries are cheap and no extra infrastructure is
required (a post_meta for almost every post is a bummer, and like others
have said that postmeta query won't be fast either).

The trick is getting enough comments that you're sure they'll be from
multiple posts. I haven't run into issues by setting it around 20, but if
you have a site where 1 post is likely to get all the recent comments you
might have to set it so high that it gets awkward, in which case your
postmeta solution could theoretically be better. That said I still think my
way is better. If you cache the results of this in a transient then fetching
even 200 comments would have a negligible impact on performance and you
aren't filling your postmeta table with junk just for this one display need.

-- 
Jeremy Clarke | http://jeremyclarke.org
Code and Design | http://globalvoicesonline.org