search

Automattic / woocommerce-subscriptions-core

Subscriptions core package for WooCommerce
Other
80 stars 29 forks source link

Use more scalable methods to filter the Orders list table by the parent order relationship on HPOS enabled sites #583

Closed james-allan closed 3 months ago

james-allan commented 3 months ago

Fixes #https://github.com/woocommerce/woocommerce-subscriptions/issues/4629

Description

On the Admin orders list table we allow users to filter the list table by subscription order types. eg renewal, parent, switch or non-subscription.

Up until now, to filter this list table by the parent orders we have used the post__in or post__not_in args.

On large sites this does not scale because the query will eventually contain a long list of order IDs that will run into a query character limit.

eg

...
[customer_note] => 
[search_filter] => all
[post__in] => Array
    (
        [4886] => 4886
        [4937] => 4937
        [4940] => 4940
        [4943] => 4943
        [4945] => 4945
        [5218] => 5218
        [5350] => 5350
        [5491] => 5491
        [5501] => 5501
        [5507] => 5507
        [5516] => 5516
        [5526] => 5526
        [5771] => 5771
        [5775] => 5775
        [5796] => 5796
        [5798] => 5798
        [5892] => 5892
        [5895] => 5895
        [5899] => 5899
....

On my local site that only has ~1200 orders and ~300 parent orders, this way of filtering results in a posts in query with over 1200 characters. If you had over 100k orders and considering order numbers get longer the more you have, you can quickly see how this would result in a very large and inefficient query and in some cases a query that eventually fails.

This PR fixes this for HPOS sites (WP posts aren't eligible see note below) by using a join or subquery.

[!note] On WP post stores this doesn't work because we're filtering the request query args directly via the request filter and there's no opportunity to filter the resulting database query clauses/args like we can on HPOS sites.

How to test this PR

  1. On trunk go to WooCommerce → Orders
  2. Using the order type filter, select "Subscription Parent" orders.

Screenshot 2024-03-25 at 4 37 46 pm

  1. Make a note of the number of results.
  2. Using the same order type filter, search for "Non subscription"
  3. Make a note of the number of results.
  4. Checkout this branch, confirm the results are the same for both order types.
  5. Repeat for both HPOS and WP Post tables.

Product impact