WordPress.org

Make WordPress Core

Opened 4 years ago

Last modified 15 months ago

#37965 new feature request

Add post_last_activity column to posts database table

Reported by: johnjamesjacoby Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Posts, Post Types Keywords: 2nd-opinion needs-patch
Focuses: performance Cc:

Description

I have a need to order posts by their most recent "activity."

Activity is purposely ambiguous, but in the context of WordPress core, this would likely mean either:

  • the publish time if no comments
  • the time of the most recent comment
  • For something like bbPress, this would mean the post_date of the most recent child post

I'm currently storing this data in postmeta, but even with type hinting, ordering meta_value by DATETIME is a slow query, particularly when there are millions of rows in both the posts and postmeta database tables.

(I'd considered repurposing the post_modified, but unwinding the way core uses this internally, paired with not wanting to confuse other developers with the oddity, along with post_modified not being an indexed column, made this a lot of work for not very much gain.)

To be efficient, this new column would require additional compound indexes akin to type_status_date. I suspect it would also need an accompanying post_last_activity_gmt column, as well as some kind of back-fill upgrade routine on existing posts.

Rather than hacking this into bbPress core in a bespoke way, or creating a separate plugin to shoe-horn this idea into all of the core queries, I figured I'd drop the feature request here for deeper discussion first, to get a read on how viable modifying core like this is to everyone.

Change History (2)

#1 @thelmachido
15 months ago

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
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

#2 @johnjamesjacoby
15 months ago

@thelmachido your approach mirrors the work done by @netweb in the bbPress Trac, so that’s a good thing!

https://bbpress.trac.wordpress.org/ticket/1925

I do still think a new column in the table would be best (even if it is unlikely) – it’s much simpler (at least for me) to understand & use, and is as human readable as the existing datetime columns.

Thank you for your example and for giving this some attention and thought. I appreciate you chiming in with such a thorough & helpful reply. I’ll think about using COALESCE again. 👍

Note: See TracTickets for help on using tickets.