Order posts by most recent comment

[Clark Wimberly]

I've been kicking around the best way to do this for a while now, so I figured it was time for a second opinion. I'm looking for a way to order my posts by most recent comment, or, if no comment, by post date. Think of it like basic forum sorting, posts with new comments go on top.

For the past few months, I've been using something close to what's described here: http://stackoverflow.com/a/3947769. It's a big nasty query and it makes mixing in simple args or paging a little more complex than it needs to be. Way back before I went with my own query, I was looking at this plugin: http://wordpress.org/extend/plugins/filter-by-comments/, which is something I'm now eyeing again. The download count and support threads are less than stellar, though.

My new plan is to get this done with post meta, accomplished in one of two ways: make my own meta key, like 'recent_activity", which I can hook to be updated on comment approval, or cheat and use something already present, like the post modified date. Normally that's updated when a post is edited, but since I've got almost no use for that, and since this field is an official 'orderby' parameter, I've been thinking about giving it a try: http://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters. Again, I'd just write a hook that updates the modified time upon comment approval.

So my question: what would you folks use to order posts by most recent activity? Mainly, I'd just love to move away from a raw SQL 'select' command and back into something a bit more comfortable. Ideas?

Clark

[Paul Menard]

Clark,

Agreed, your best bet is always to NOT resort to raw SQL. Not that there is anything tricky about this. But it could break if things at the table level change ever. 

I would do mostly what you mentioned. Intercept the action of the comment moving from some state to 'comment_approved' Like here http://codex.wordpress.org/Plugin_API/Action_Reference/comment_(old_status)_to_(new_status) Though you should also handle when moving from 'comment_approved' to something else like trashed. 

The action function you setup would simple add/update a post meta record. Personally I would call the post meta key something descriptive like 'recent_comment_date'. 

So once you have the post meta values populated you would WP_Query instance to order by the value of the meta value http://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters

http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters 

I would NOT use or try to update the post dates. but that is just me. If you update the post_date field this will also order the listing within wp-admin showing the posts in last comment date order. 

P-

--
You received this message because you are subscribed to the Google Groups "WordPress Austin" group.
To post to this group, send email to wordpres...@googlegroups.com
To unsubscribe from this group, send email to wordpress-aust...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/wordpress-austin?hl=en
 
Our meeting information is available at http://www.meetup.com/austinwordpress/

[Clark Wimberly]

Thanks Paul, that mostly confirms what I was thinking.

The only tiny difference is I'm thinking about using the post modified date, not the actual post date itself. I mainly leaned that way because I noticed it was a supported orderby parameter, and because recently I've seen grumblings that post meta queries are inherently slower (since they reference two columns instead of staying just within wp_posts).

[Clark Wimberly]

A quick update, I tried the Filter By Comments plugin and it works pretty dang well: http://wordpress.org/extend/plugins/filter-by-comments/

I'll report back if I find any issues, but for now, I'm considering this solved.