[wp-trac] [WordPress Trac] #37965: Add post_last_activity column to posts database table

[WordPress Trac]

#37965: Add post_last_activity column to posts database table
-------------------------------------+------------------------------
 Reporter:  johnjamesjacoby          |       Owner:  (none)
     Type:  feature request          |      Status:  new
 Priority:  normal                   |   Milestone:  Awaiting Review
Component:  Posts, Post Types        |     Version:
 Severity:  normal                   |  Resolution:
 Keywords:  2nd-opinion needs-patch  |     Focuses:  performance
-------------------------------------+------------------------------

Comment (by thelmachido):

 Hi @johnjamesjacoby

 To order posts with according to their most recent activity one would have
 to create a custom $wpdb query because it isn't possible using the
 WP_Query object.
 Use the MySQL COALESCE() Function to make sure the query will pick the
 most recent comment date of **a** post and not of all comments of all
 posts as a whole using the condition **where wpc.comment_post_id =
 wp_posts.id**''''.
 {{{#!php
 <?php
 select wp_posts.*,
     coalesce(
         (
             select max(comment_date)
             from $wpdb->comments wpc
             where wpc.comment_post_id = wp_posts.id
         ),
         wp_posts.post_date
     ) as mcomment_date
     from $wpdb->posts wp_posts
     where post_type = 'post'
     and post_status = 'publish'
     order by mcomment_date desc
     limit 10
 }}}

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/37965#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform