Add post_last_activity column to posts database table

[johnjamesjacoby]

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.

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

[johnjamesjacoby]

@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. 👍

[pbearne]

Is this still a thing?