WordPress.org

Make WordPress Core

Opened 7 months ago

Last modified 3 months ago

#46294 new defect (bug)

wp rest api fails to paginate page requests correctly when ordering on menu_order

Reported by: hobzhobz Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Query Keywords:
Focuses: rest-api Cc:

Description

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.

Change History (7)

#1 @mukesh27
7 months ago

  • Component changed from General to REST API
  • Focuses rest-api added

#2 @hobzhobz
7 months ago

  • Component changed from REST API to General
  • Focuses rest-api removed

Here is a hack to fix the issue:

In the file /wp-includes/rest-api/endpoints/class-wp-rest-posts-controller.php:290

around line 290.
change from:

$posts_query  = new WP_Query();
$query_result = $posts_query->query( $query_args );

to

$posts_query  = new WP_Query();
if (isset($query_args['orderby']) && is_string($query_args['orderby']) && $query_args['orderby'] == 'menu_order') {
    $query_args['orderby'] = ['menu_order' => 'ASC', 'ID' => 'ASC'];
}
$query_result = $posts_query->query( $query_args );

now the query will be deterministic between pages.

#3 @hobzhobz
7 months ago

Note, this is hard to reproduce unless you set menu_order != 0 on many pages.

If menu_order == 0 on all pages this issue doesnt seem to happen, but i bet it has to do with mysql internals as its "undefined behavior" how mysql sorts when sorting only on a non unique field.

#4 @swissspidy
7 months ago

  • Component changed from General to REST API

This ticket was mentioned in Slack in #core-restapi by timothybjacobs. View the logs.


3 months ago

#6 follow-up: @TimothyBlynJacobs
3 months ago

  • Component changed from REST API to Query
  • Focuses rest-api added

This seems like a bug in the query component. I’d think paginating over a new WP_Query([ 'orderby' => 'menu_order' ]) should work as expected.

#7 in reply to: ↑ 6 @hobzhobz
3 months ago

Replying to TimothyBlynJacobs:

This seems like a bug in the query component. I’d think paginating over a new WP_Query([ 'orderby' => 'menu_order' ]) should work as expected.

I agree. Wp_query should automatically append order by id asc or something when the original order by clause contains only non-unique fields

Note: See TracTickets for help on using tickets.