new sort method for query_posts (order by last comment date)

[erden.ozkan]

I think we need sorting by latest post comment ability for query_posts. "Recent comments" plugins does this but we need this sorting in the core.

BBPress is planned as a WP plugin according latest chat logs posted by Matt. We gonna have to need this anyway.

[wonderboymusic]

Gonna move this into 3.7 for review. It's actually a pretty easy patch. 11398.diff​ adds comment_date as an allowed key for orderby:

$posts = get_posts( array( 'fields' => 'ids', 'orderby' => 'comment_date' ) );

The big issue is that it requires a JOIN of posts and comments which won't work if the tables are partitioned or something. This could be an option for the user, but maybe something that core never uses.

[wonderboymusic]

updated to LEFT JOIN, my posts_per_page was wrong so I didn't notice the limit was off

[wonderboymusic]

.2.diff has Unit Tests - tests revealed the need for:

$orderby = "ISNULL($wpdb->comments.comment_date), $wpdb->comments.comment_date";

[nacin]

Replying to wonderboymusic:

The big issue is that it requires a JOIN of posts and comments which won't work if the tables are partitioned or something. This could be an option for the user, but maybe something that core never uses.

WordPress assumes that all blog-level tables are in the same dataset can be joined, including posts, terms, comments, and the associated meta tables. Users is considered a global table, so it cannot be joined with comments, posts, etc. (and is not in core).

[wonderboymusic]

hence meta_query all over the B*Press - this is unfortunate

[wonderboymusic]

Ideas via @nacin and @jjj in IRC:

1. sort by comment date
2. put uncommented posts on the top, on the bottom, or ignore them completely
3. uncommented posts should instead be sorted by post_date (possible with a CASE clause)
4. sort by "freshest" - which may mean post_modified?

[Presskopp]

related: #25180

Trac ticket: https://core.trac.wordpress.org/ticket/11398