WordPress.org

Make WordPress Core

Opened 3 months ago

Last modified 3 weeks ago

#47642 new defect (bug)

Order by comment count - posts list tables

Reported by: alektabor Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 5.2.2
Component: Posts, Post Types Keywords: has-screenshots has-patch needs-testing
Focuses: administration Cc:
PR Number:

Description

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.
https://i.imgur.com/HupwtnS.png
Set sorting by comment count in comments column.
https://i.imgur.com/zFDU7Oj.png
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.

https://i.imgur.com/tWevfpW.png

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:
https://i.imgur.com/rFyTHjE.png

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:
https://i.imgur.com/THMM1Ri.png

What's funny if I added 'wp_posts.post_title' to query the problem is solved (unique posts):
https://i.imgur.com/nxn3tCy.png
https://i.imgur.com/whvDFaI.png

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.

Attachments (2)

patch.47642.20190920.1 (777 bytes) - added by ramon fincken 4 weeks ago.
patch.47642.20190920.1
patch.47642.20190923.1 (766 bytes) - added by ramon fincken 3 weeks ago.
patch.47642.20190923.1

Download all attachments as: .zip

Change History (9)

#1 @ramon fincken
4 weeks ago

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?

#2 @ramon fincken
4 weeks ago

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

#3 @ramon fincken
4 weeks ago

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
4 weeks ago

patch.47642.20190920.1

#4 @ramon fincken
4 weeks ago

  • Keywords has-patch needs-testing added; needs-patch removed

#5 @ramon fincken
4 weeks ago

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

#6 @johnbillion
3 weeks ago

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.

#7 @ramon fincken
3 weeks ago

yes that would be totally unique. See attached new patch

@ramon fincken
3 weeks ago

patch.47642.20190923.1

Note: See TracTickets for help on using tickets.