Commit 69c1c333754 for woocommerce
commit 69c1c33375490c46eb05b9bc607519c2ac218e11
Author: Christopher Hertel <mail@christopher-hertel.de>
Date: Tue Nov 10 11:34:39 2026 +0100
Break sort ties by ID in the paged query abilities (#70748)
* Break sort ties by ID in the paged query abilities
products-query, orders-query and product-terms-query sorted only by date,
the chosen order field, or the term name. Items that tied on that field
could repeat on two pages or appear on none. Each now ends its sort on the
ID, in the requested direction.
On posts storage, orders-query sorts the total through the _order_total
meta key itself, because the data store maps orderby "total" only when it
is a plain string. Terms get the tiebreak through terms_clauses, attached
only for the ability's own query.
* Drop the unused order arg and test both sort directions
Every orders-query sort is now an array that carries its own direction,
so the top-level order arg is read by neither storage. The tied-orders
and tied-terms tests now cover both directions.
* Use order totals that sort differently as text in the total sort test
diff --git a/plugins/woocommerce/changelog/wooai-1580-query-paging b/plugins/woocommerce/changelog/wooai-1580-query-paging
new file mode 100644
index 00000000000..435118f173b
--- /dev/null
+++ b/plugins/woocommerce/changelog/wooai-1580-query-paging
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Stop products, orders and product terms from repeating or going missing across pages of the query abilities.
diff --git a/plugins/woocommerce/src/Internal/Abilities/Domain/OrdersQuery.php b/plugins/woocommerce/src/Internal/Abilities/Domain/OrdersQuery.php
index 67d17ebb73e..3b8a5aa62ff 100644
--- a/plugins/woocommerce/src/Internal/Abilities/Domain/OrdersQuery.php
+++ b/plugins/woocommerce/src/Internal/Abilities/Domain/OrdersQuery.php
@@ -144,10 +144,8 @@ class OrdersQuery extends AbstractDomainAbility implements AbilityDefinition {
'type' => self::get_order_type_from_input( $input ),
);
- foreach ( array( 'billing_email', 'order' ) as $field ) {
- if ( ! empty( $input[ $field ] ) ) {
- $args[ $field ] = $input[ $field ];
- }
+ if ( ! empty( $input['billing_email'] ) ) {
+ $args['billing_email'] = $input['billing_email'];
}
$args['status'] = $input['status'] ?? array();
@@ -158,9 +156,7 @@ class OrdersQuery extends AbstractDomainAbility implements AbilityDefinition {
$args['status'] = array_map( array( __CLASS__, 'prepare_status_arg' ), (array) $args['status'] );
- if ( ! empty( $input['orderby'] ) ) {
- $args['orderby'] = self::prepare_orderby_arg( $input['orderby'] );
- }
+ $args = array_merge( $args, self::prepare_orderby_args( $input['orderby'] ?? 'date', $input['order'] ?? 'desc' ) );
foreach ( array( 'customer_id', 'parent' ) as $field ) {
if ( isset( $input[ $field ] ) ) {
@@ -520,23 +516,41 @@ class OrdersQuery extends AbstractDomainAbility implements AbilityDefinition {
}
/**
- * Prepare orderby values for wc_get_orders across HPOS and legacy storage.
- *
- * Values not present in the map are already accepted by wc_get_orders for
- * both storage engines and pass through unchanged.
+ * Sort args for wc_get_orders that end on the order ID, so orders that tie on the
+ * sort field keep one order across pages. Posts storage sorts the total only when
+ * orderby is the plain string, so the total needs its meta key here.
*
* @param string $orderby Input orderby value.
- * @return string
+ * @param string $order Input sort direction.
+ * @return array<string, mixed>
*/
- private static function prepare_orderby_arg( string $orderby ): string {
- $orderby_map = array(
- 'id' => 'ID',
- );
+ private static function prepare_orderby_args( string $orderby, string $order ): array {
+ $order = strtoupper( $order );
- if ( 'date_modified' === $orderby ) {
- return OrderUtil::custom_orders_table_usage_is_enabled() ? 'date_modified' : 'post_modified';
+ if ( 'id' === $orderby ) {
+ return array( 'orderby' => array( 'ID' => $order ) );
}
- return $orderby_map[ $orderby ] ?? $orderby;
+ $hpos = OrderUtil::custom_orders_table_usage_is_enabled();
+ if ( 'total' === $orderby && ! $hpos ) {
+ return array(
+ 'orderby' => array(
+ 'meta_value_num' => $order,
+ 'ID' => $order,
+ ),
+ 'meta_key' => '_order_total', // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- The same join the data store uses for its own total sort.
+ );
+ }
+
+ if ( 'date_modified' === $orderby && ! $hpos ) {
+ $orderby = 'post_modified';
+ }
+
+ return array(
+ 'orderby' => array(
+ $orderby => $order,
+ 'ID' => $order,
+ ),
+ );
}
}
diff --git a/plugins/woocommerce/src/Internal/Abilities/Domain/ProductTermsQuery.php b/plugins/woocommerce/src/Internal/Abilities/Domain/ProductTermsQuery.php
index 7b611f89a92..bb391be9739 100644
--- a/plugins/woocommerce/src/Internal/Abilities/Domain/ProductTermsQuery.php
+++ b/plugins/woocommerce/src/Internal/Abilities/Domain/ProductTermsQuery.php
@@ -123,7 +123,9 @@ class ProductTermsQuery extends AbstractDomainAbility implements AbilityDefiniti
$args['object_ids'] = array( $input['product'] );
}
+ add_filter( 'terms_clauses', array( __CLASS__, 'add_id_tiebreak' ), PHP_INT_MAX );
$terms = get_terms( $args );
+ remove_filter( 'terms_clauses', array( __CLASS__, 'add_id_tiebreak' ), PHP_INT_MAX );
if ( is_wp_error( $terms ) ) {
return $terms;
}
@@ -161,6 +163,27 @@ class ProductTermsQuery extends AbstractDomainAbility implements AbilityDefiniti
);
}
+ /**
+ * End the sort on the term ID, so terms that tie on the sort field keep one order
+ * across pages. Runs last, so other terms_clauses callbacks can't drop it.
+ *
+ * @internal
+ *
+ * @since 11.4.0
+ *
+ * @param array $clauses Term query clauses.
+ * @return array
+ */
+ public static function add_id_tiebreak( $clauses ) {
+ if ( ! is_array( $clauses ) || empty( $clauses['orderby'] ) || ! is_string( $clauses['orderby'] ) ) {
+ return $clauses;
+ }
+
+ $clauses['orderby'] .= ' ' . ( $clauses['order'] ?? '' ) . ', t.term_id';
+
+ return $clauses;
+ }
+
/**
* Format a term with the fields of the WooCommerce term reads.
*
diff --git a/plugins/woocommerce/src/Internal/Abilities/Domain/ProductsQuery.php b/plugins/woocommerce/src/Internal/Abilities/Domain/ProductsQuery.php
index 51de9a32358..1a048b4eac1 100644
--- a/plugins/woocommerce/src/Internal/Abilities/Domain/ProductsQuery.php
+++ b/plugins/woocommerce/src/Internal/Abilities/Domain/ProductsQuery.php
@@ -125,6 +125,10 @@ class ProductsQuery extends AbstractDomainAbility implements AbilityDefinition {
'page' => $page,
'paginate' => true,
'return' => 'objects',
+ 'orderby' => array(
+ 'date' => 'DESC',
+ 'ID' => 'DESC',
+ ),
);
foreach ( array( 'status', 'stock_status' ) as $field ) {
diff --git a/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/OrdersQueryTest.php b/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/OrdersQueryTest.php
index 49e8920e2e1..7b381f1d61f 100644
--- a/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/OrdersQueryTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/OrdersQueryTest.php
@@ -3,6 +3,7 @@ declare( strict_types = 1 );
namespace Automattic\WooCommerce\Tests\Internal\Abilities\Domain;
+use Automattic\WooCommerce\Enums\OrderStatus;
use Automattic\WooCommerce\Internal\Abilities\Domain\OrdersQuery;
use Automattic\WooCommerce\RestApi\UnitTests\HPOSToggleTrait;
use Automattic\WooCommerce\RestApi\UnitTests\Helpers\OrderHelper;
@@ -333,6 +334,119 @@ class OrdersQueryTest extends WC_Unit_Test_Case {
$this->assertSame( 400, $result->get_error_data()['status'] );
}
+ /**
+ * @testdox Should return each order exactly once across pages when orders tie on $orderby $order, with HPOS $hpos.
+ * @testWith [true, "date", "asc"]
+ * [true, "date", "desc"]
+ * [true, "date_modified", "asc"]
+ * [true, "date_modified", "desc"]
+ * [true, "total", "asc"]
+ * [true, "total", "desc"]
+ * [true, "id", "asc"]
+ * [true, "id", "desc"]
+ * [false, "date", "asc"]
+ * [false, "date", "desc"]
+ * [false, "date_modified", "asc"]
+ * [false, "date_modified", "desc"]
+ * [false, "total", "asc"]
+ * [false, "total", "desc"]
+ * [false, "id", "asc"]
+ * [false, "id", "desc"]
+ * @param bool $hpos Whether to use the custom orders table.
+ * @param string $orderby Sort field.
+ * @param string $order Sort direction.
+ */
+ public function test_pages_tied_orders_once_each( bool $hpos, string $orderby, string $order ): void {
+ $this->use_storage( $hpos );
+ $ids = array();
+ for ( $i = 0; $i < 7; $i++ ) {
+ $ids[] = $this->create_dated_order( 10 );
+ }
+ $this->set_modified_date( $ids, $hpos );
+
+ $paged = array();
+ for ( $page = 1; $page <= 3; $page++ ) {
+ $result = OrdersQuery::execute(
+ array(
+ 'orderby' => $orderby,
+ 'order' => $order,
+ 'page' => $page,
+ 'per_page' => 3,
+ )
+ );
+ $this->assertIsArray( $result );
+ $paged = array_merge( $paged, array_column( $result['orders'], 'id' ) );
+ }
+
+ if ( 'desc' === $order ) {
+ rsort( $ids );
+ }
+ $this->assertSame( $ids, $paged, 'Tied orders should be sorted by ID in the requested direction.' );
+ }
+
+ /**
+ * @testdox Should sort orders by total.
+ *
+ * @dataProvider storage_modes
+ * @param bool $hpos Whether to use the custom orders table.
+ */
+ public function test_sorts_by_total( bool $hpos ): void {
+ $this->use_storage( $hpos );
+ $hundred = $this->create_dated_order( 100 );
+ $nine = $this->create_dated_order( 9 );
+ $ten = $this->create_dated_order( 10 );
+
+ $result = OrdersQuery::execute(
+ array(
+ 'orderby' => 'total',
+ 'order' => 'asc',
+ )
+ );
+
+ $this->assertSame( array( $nine, $ten, $hundred ), array_column( $result['orders'], 'id' ) );
+ }
+
+ /**
+ * Create a processing order with a fixed creation date.
+ *
+ * @param float $total Order total.
+ * @return int
+ */
+ private function create_dated_order( float $total ): int {
+ $order = wc_create_order();
+ $order->set_status( OrderStatus::PROCESSING );
+ $order->set_date_created( '2026-01-01T10:00:00' );
+ $order->set_total( $total );
+ $order->save();
+
+ return $order->get_id();
+ }
+
+ /**
+ * Give orders one modified date in the table the query reads, since saving an order sets it to now.
+ *
+ * @param int[] $ids Order IDs.
+ * @param bool $hpos Whether the custom orders table stores the orders.
+ */
+ private function set_modified_date( array $ids, bool $hpos ): void {
+ global $wpdb;
+
+ foreach ( $ids as $id ) {
+ if ( $hpos ) {
+ $wpdb->update( OrderUtil::get_table_for_orders(), array( 'date_updated_gmt' => '2026-01-02 10:00:00' ), array( 'id' => $id ) );
+ } else {
+ $wpdb->update(
+ $wpdb->posts,
+ array(
+ 'post_modified' => '2026-01-02 10:00:00',
+ 'post_modified_gmt' => '2026-01-02 10:00:00',
+ ),
+ array( 'ID' => $id )
+ );
+ }
+ }
+ }
+
/**
* Switch the order storage for the current test.
*
diff --git a/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductTermsQueryTest.php b/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductTermsQueryTest.php
index b0384257620..2dfde379c53 100644
--- a/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductTermsQueryTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductTermsQueryTest.php
@@ -673,4 +673,49 @@ class ProductTermsQueryTest extends \WC_Unit_Test_Case {
$this->assertWPError( $result );
$this->assertSame( 'ability_invalid_input', $result->get_error_code() );
}
+
+ /**
+ * @testdox Should return each term exactly once across pages when terms share a name, paging by $paging in $order order.
+ * @testWith ["page", "asc"]
+ * ["page", "desc"]
+ * ["offset", "asc"]
+ * @param string $paging Paging input.
+ * @param string $order Sort direction.
+ */
+ public function test_pages_terms_with_the_same_name_once_each( string $paging, string $order ): void {
+ $ids = array();
+ for ( $i = 0; $i < 5; $i++ ) {
+ $parent = self::factory()->term->create(
+ array(
+ 'taxonomy' => 'product_cat',
+ 'name' => 'Parent ' . $i,
+ )
+ );
+ $ids[] = self::factory()->term->create(
+ array(
+ 'taxonomy' => 'product_cat',
+ 'name' => 'Twin',
+ 'parent' => $parent,
+ )
+ );
+ }
+
+ $paged = array();
+ for ( $page = 1; $page <= 3; $page++ ) {
+ $input = array(
+ 'taxonomy' => 'product_cat',
+ 'search' => 'Twin',
+ 'order' => $order,
+ 'per_page' => 2,
+ );
+ $input += 'page' === $paging ? array( 'page' => $page ) : array( 'offset' => ( $page - 1 ) * 2 );
+ $result = ProductTermsQuery::execute( $input );
+ $paged = array_merge( $paged, array_column( $result['terms'], 'id' ) );
+ }
+
+ if ( 'desc' === $order ) {
+ rsort( $ids );
+ }
+ $this->assertSame( $ids, $paged, 'Terms with the same name should be sorted by ID in the requested direction.' );
+ }
}
diff --git a/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductsQueryTest.php b/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductsQueryTest.php
index 071422c6ae4..2e805dfb39e 100644
--- a/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductsQueryTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/Abilities/Domain/ProductsQueryTest.php
@@ -221,6 +221,33 @@ class ProductsQueryTest extends WC_Unit_Test_Case {
$this->assertTrue( rest_validate_value_from_schema( $result, $schema, 'products' ) );
}
+ /**
+ * @testdox Should return each product exactly once across pages when products share a creation date.
+ */
+ public function test_pages_products_with_the_same_date_once_each(): void {
+ $ids = array();
+ for ( $i = 0; $i < 7; $i++ ) {
+ $product = WC_Helper_Product::create_simple_product();
+ $product->set_date_created( '2026-01-01T10:00:00' );
+ $product->save();
+ $ids[] = $product->get_id();
+ }
+
+ $paged = array();
+ for ( $page = 1; $page <= 3; $page++ ) {
+ $result = ProductsQuery::execute(
+ array(
+ 'page' => $page,
+ 'per_page' => 3,
+ )
+ );
+ $paged = array_merge( $paged, array_column( $result['products'], 'id' ) );
+ }
+
+ rsort( $ids );
+ $this->assertSame( $ids, $paged, 'Products with the same date should be sorted by ID, newest first.' );
+ }
+
/**
* Query one product by ID and check the result against the output schema.
*