[wp-trac] [WordPress Trac] #46294: wp rest api fails to paginate page requests correctly when ordering on menu_order

[WordPress Trac]

#46294: wp rest api fails to paginate page requests correctly when ordering on
menu_order
--------------------------+-----------------------------
 Reporter:  hobzhobz      |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 **Describe the bug**
 Gutenberg and wordpress fails to render the parent page dropdown correctly
 when there are more than 100 pages and the pages have various menu_order
 values set.

 The problem is that gutenberg calls the wordpress rest api like this,
 getting 100 items at a time.

 http://server/wp-
 json/wp/v2/pages?per_page=100&exclude=890&parent_exclude=890&orderby=menu_order&order=asc&context=edit&_locale=user
 http://server/wp-
 json/wp/v2/pages?per_page=100&exclude%5B0%5D=890&parent_exclude%5B0%5D=890&orderby=menu_order&order=asc&context=edit&_locale=user&page=2

 This results in the following 2 sql queries:
 `SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND
 wp_posts.ID NOT IN (890) AND wp_posts.post_parent NOT IN (890) AND
 wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER
 BY wp_posts.menu_order ASC LIMIT 0, 100`
 `SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND
 wp_posts.ID NOT IN (890) AND wp_posts.post_parent NOT IN (890) AND
 wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER
 BY wp_posts.menu_order ASC LIMIT 100, 100`

 When ordering on a non unique field like menu_order you need to also add a
 second unique ordering field like ID ,, See
 https://bugs.mysql.com/bug.php?id=72076 See the answer from Tor Didriksen.

 This is very critical bug because it means that if the current pages
 parent is missing in the parent page dropdown and you click update it
 means that the menu order for that page will be reset and this means i
 cant use gutenberg.

 **To Reproduce**
 Steps to reproduce the behavior:

 1. Create around 200 pages.
 2. Set menu_order != on some of the pages.
 3. edit any of the pages.
 4. the parent page dropdown is missing pages

 **Expected behavior**
 the rest api should add some unique order by field to prevent the same
 items from coming on page 1 and 2 of the requests.

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