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

[Christian Foster]

Thanks for your replies - I went for a timestamp in a post meta field
- when a comment is inserted it updates the post with the timestamp of
the comment, and when a post is published it also gets a timestamp.
This way they can both be in the list with/without comments and we
just sort on one meta field.

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.

On Mon, Sep 27, 2010 at 10:21 PM, Mike Schinkel
<mikeschinkel at newclarity.net> wrote:
> 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
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>