Order by comment count - posts list tables

[alektabor]

Results from posts list tables with enabled ordering by comment count are mixed (posts are missing and aren't unique). Pagination required.
Links:
wp-admin/edit.php?orderby=comment_count&order=asc
wp-admin/edit.php?orderby=comment_count&order=asc&paged=2
No matter which post type.
You need to set 'Number of items per page' in screen options in order to get a pagination.

Set sorting by comment count in comments column.

In lists of posts some of them are missing. Page number 1 and number 2 don't have unique posts.
I was testing it for some cases:
30+ posts and 'posts_per_page' = 20
30+ posts and 'posts_per_page' = 10
250+ posts and 'posts_per_page' = 20
24 pages and 'posts_per_page' = 20
Results in all cases were wrong.

The official queries are from wp-includes/class-wp-query.php (line: 2984) ​https://github.com/WordPress/WordPress/blob/master/wp-includes/class-wp-query.php#L2984, variable: $this->request. Below applied filter are generated ids for current posts.

SQL queries from debugging (official queries):

Page number 1 (paged=1):

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.comment_count ASC LIMIT 0, 20

Result:

Page number 1 (paged=2):

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.comment_count ASC LIMIT 20, 20

Result:

What's funny if I added 'wp_posts.post_title' to query the problem is solved (unique posts):

I think that problem is actually in the database queries/ settings what's caused the bad output in admin's panel.

For other sorting like title or date this problem doesn't occur.

[ramon fincken]

alltough I see that your ID resultset in mysql has duplicates I cannot reproduce this using:

posts with 0, 1, 2, 3, 3, 4 comments
paginated 2

I do not have overlapping or missing posts in the list.

Still this might me an error because of the post_status. Could you run the queries and do them only for the publish post status?

[ramon fincken]

OK, reproduced this .. it has to do with the fact that te results are ordered by a column that has the very same information. Thus is *might* be possible that de database engine spits the results to PHP in a (lets call it) *random* order. Thanks @herregroen for pointing that out

[ramon fincken]

so .. here is the fix. We need an extra unique (!) colum to sort by AFTER the comment count has been sorted. I used post_modified in this case.

[ramon fincken]

patch.47642.20190920.1

[ramon fincken]

for the record: tested with pagination of 20 using 23 posts ( all had no comments )

[johnbillion]

Thanks for the patch @ramon-fincken. The post_modified field is also non-unique. It's possible that two posts could share the same comment count and modified date.

The secondary sort order should probably be the post ID instead.

[ramon fincken]

yes that would be totally unique. See attached new patch

[ramon fincken]

patch.47642.20190923.1