WordPress.org

Make WordPress Core

Opened 14 months ago

Last modified 5 weeks ago

#47642 reviewing defect (bug)

Order by comment count - posts list tables

Reported by: alektabor Owned by: johnbillion
Milestone: 5.6 Priority: normal
Severity: normal Version:
Component: Posts, Post Types Keywords: has-screenshots has-patch needs-testing
Focuses: administration Cc:

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 11 months ago.
patch.47642.20190920.1
patch.47642.20190923.1 (766 bytes) - added by ramon fincken 11 months ago.
patch.47642.20190923.1

Download all attachments as: .zip

Change History (14)

#1 @ramon fincken
11 months 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
11 months 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
11 months 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
11 months ago

patch.47642.20190920.1

#4 @ramon fincken
11 months ago

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

#5 @ramon fincken
11 months ago

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

#6 @johnbillion
11 months 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
11 months ago

yes that would be totally unique. See attached new patch

@ramon fincken
11 months ago

patch.47642.20190923.1

#8 @ramon fincken
9 months ago

Hi @johnbillion any chance of checking the last patch?

#9 @JavierCasares
4 months ago

It works for me... also, to maintain consistency, should we check the same thing for the "modified" (default) option?

So, if we order and modified two posts at the same exact time... we may have the same problem, so it should be and option to order by the modified and ID field.

#10 @ramon fincken
4 months ago

Chances are that you collide with 2 very same (second) updates are less than having posts with zero comments if you ask me.

#11 @ramon fincken
5 weeks ago

If anything more needs to be done @johnbillion please let me know

#12 @johnbillion
5 weeks ago

  • Milestone changed from Awaiting Review to 5.6
  • Owner set to johnbillion
  • Status changed from new to reviewing
  • Version 5.2.2 deleted
Note: See TracTickets for help on using tickets.