[wp-trac] [WordPress Trac] #47642: Order by comment count - posts list tables

[WordPress Trac]

#47642: Order by comment count - posts list tables
-------------------------------+-----------------------------------------
 Reporter:  alektabor          |      Owner:  (none)
     Type:  defect (bug)       |     Status:  new
 Priority:  normal             |  Milestone:  Awaiting Review
Component:  Posts, Post Types  |    Version:  5.2.2
 Severity:  normal             |   Keywords:  needs-patch has-screenshots
  Focuses:  administration     |
-------------------------------+-----------------------------------------
 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.
 [[Image(https://i.imgur.com/HupwtnS.png)]]
 Set sorting by comment count in comments column.
 [[Image(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.

 [[Image(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:
 [[Image(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:
 [[Image(https://i.imgur.com/THMM1Ri.png)]]

 What's funny if I added **'wp_posts.post_title'** to query the problem is
 solved (unique posts):
 [[Image(https://i.imgur.com/nxn3tCy.png)]]
 [[Image(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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/47642>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform