Cache subscription last order date #819

alefesouza · 2025-03-21T01:45:30Z

Fixes #782

Description

This PR improves the subscription list performance by caching the subscription last order date when new renewals are created or when the last order is deleted.

How to test this PR

Subscriptions list (existing subscription handling)

Open phpMyAdmin.
Run the following SQL Query:

SELECT * FROM `wp_wc_orders_meta` WHERE `meta_key` LIKE '%_last_order_date_created%'

There should be no results.
Open wp-admin and access WooCommerce > Subscriptions.
Refresh the query on wp-admin, there should be a _last_order_date_created item for every loaded subscription on the subscriptions page.
Add a breakpoint here and start the debugger.
Reload the subscription page.
If the debugger stops at the breakpoint, it means the data is being loaded from the cached metadata (_last_order_date_created) and it's not loading the all subscription orders as before.
The Last Order Date column should be correctly rendered.

Subscription renewal

Open any subscription.
Look the _last_order_date_created value for the chosen subscription on phpMyAdmin.
On Order actions, run Process renewal.
The _last_order_date_created value for the chosen subscription should be updated.
Open the subscriptions page.
The Last Order Date column for the chosen subscription should show the correct date.

Subscription order deletion

Open any subscription with renewal orders.
Look the _last_order_date_created value for the chosen subscription on phpMyAdmin.
Move to trash the subscription latest order.
Permanently delete the order on the trashed order list.
The _last_order_date_created value for the chosen subscription should be updated to the new latest order.
The Last Order Date column for the chosen subscription should show the correct date.

Manual parent ID changes

Open any subscription.
On this line, add the following code:

add_action(
	'woocommerce_admin_order_data_after_order_details',
		function () {
			$subscription = wcs_get_subscription( 259 ); // Choose any subscription
			$subscription->set_parent_id( 256 ); // Choose any subscription order from another subscription
			$subscription->save();
		},
	10
);

Open the chosen subscription.
Open the Subscriptions page.
If the new subscription parent order id is higher than the other others, its date should show up on the Last Order Date column.
The Last Order Date column for the old parent id should also be updated.

Add order relation

Open any subscription.
On this line, add the following code:

add_action(
	'woocommerce_admin_order_data_after_order_details',
		function () {
			$order = wc_get_order( 282 ); // Choose any child order
			$subscription = wcs_get_subscription( 259 ); // Choose any subscription

			WCS_Related_Order_Store::instance()->add_relation( $order, $subscription, 'renewal' );
		},
	10
);

Open the chosen subscription.
Open the Subscriptions page.
If the new subscription related order id is higher than the other others, its date should show up on the Last Order Date column.

Delete order relation

Open any subscription.
On this line, add the following code:

add_action(
	'woocommerce_admin_order_data_after_order_details',
		function () {
			$subscription = wcs_get_subscription( 259 ); // Choose any subscription
			$order = wc_get_order( 282 ); // Choose the latest child order

			WCS_Related_Order_Store::instance()->delete_relation( $order, $subscription, 'renewal' );
		},
	10
);

Open the chosen subscription.
Open the Subscriptions page.
The Last Order Date column should show the date of the new latest order.

Product impact

Added changelog entry (or does not apply)
Will this PR affect WooCommerce Subscriptions? yes/no/tbc, add issue ref
Will this PR affect WooCommerce Payments? yes/no/tbc, add issue ref
Added deprecated functions, hooks or classes to the spreadsheet

mattallan

Thanks @alefesouza for picking up this issue!

I like your approach with only using this new cached date value for display purposes and not in calculations as that reduces the risk of any serious breakages 😅 I think this is a good starting position.

Taking a look at the last_order_date_created date, it's currently calculated each time by fetching the list of parent, renewal, switch orders linked to the subscription, and then gets maximum order ID from this list. Reads the order into memory and then returns the data_created value.

In the crazy world of subscriptions, some subscriptions don't have parent orders, some renewal orders can be created manually & the link between subscription and related orders can be freely added and removed using code like:

WCS_Related_Order_Store::instance()->add_relation( $order, $subscription, 'renewal' ); // Manually connects a renewal order to a subscription.
...
WCS_Related_Order_Store::instance()->delete_relation( $order, $subscription, 'switch' ); // Unlinks a switch order from a subscription.

With that all said, to ensure we're displaying the most up-to-date Last Order Data value on the WooCommerce > Subscriptions table, we'll need to make sure we're updating/recalculating the cache for each of the following cases:

Parent order changes

When someone manually assigns a new parent order to a subscription i.e

$subscription->set_parent_id( 1234 );
$subscription->save();

When creating a pending order via the Subscription Actions
When purchasing a new subscription through the checkout

Renewal order changes

When a new renewal order is manually created via the Subscription Actions
When a new renewal is automatically created via action scheduler
When a payment gateway manually creates a renewal order for gateway scheduled renewals (i.e stripe billing, paypal subscriptions)
When an existing renewal order is manually linked to a subscription via WCS_Related_Order_Store::instance()->add_relation()
When a renewal order is deleted via any means
When a renewal order is unlinked WCS_Related_Order_Store::instance()->delete_relation()

Switch order changes

When a customer processes a upgrade/downgrade via the My Account
When an order is manually linked as a switch on a subscription WCS_Related_Order_Store::instance()->add_relation()
When a switch order is deleted or unlinked via delete_relation

Having to support this list of cases may seem a bit intimidating but I think one way we could achieve this is to have a new class that makes use of our WCS_Object_Data_Cache_Manager (for HPOS) and WCS_Post_Meta_Cache_Manager (WP Posts).

I haven't explored this yet myself, but I think we should be able to instantiate a new WCS_Object_Data_Cache_Manager|WCS_Post_Meta_Cache_Manager class and pass it 'subscription' order type and then a list of meta keys that we want to "watch". Something like:

$cache_manager = new WCS_Object_Data_Cache_Manager( 'subscription', [
 'parent_id', // don't know if this works.
 '_subscription_renewal_order_ids_cache', // don't know if this will work for internal meta keys.
 '_subscription_switch_order_ids_cache',
] );
$cache_manager->init();

The thinking behind this idea is that our related orders cache handler is already watching for all of these changes to renewal and switch orders and keeping the related order cache up-to-date. So in theory, we should be able to watch for changes to these related orders cache and then trigger recalculating the Last Order Date.

The only meta changes we're currently not watching which also affects the last order date, is the parent ID. I'm curious if we can watch for updates to the parent_id meta on the subscription to know when a new parent order is set (?) 🤔

There's a high chance I'm over complicating this, or it simply doesn't work, and so very simple approach might be to just to hook onto the woocommerce_update_subscription hook and delete the date cache. This will then trigger us to recalculate the date the next time it's fetched.

Keen to hear your thoughts on this! :)

🥚 Hope you guys are all good and good luck with everything! <3

alefesouza · 2025-04-07T16:37:34Z

@leonardola this PR is ready to review

leonardola · 2025-04-07T18:42:08Z

includes/class-wc-subscriptions-order.php

+
+		add_action( 'wcs_orders_add_relation', [ __CLASS__, 'add_relation_update_order_related_subscriptions_last_order_date_created' ], 10, 3 );
+
+		add_action( 'wcs_orders_delete_relation', [ __CLASS__, 'delete_relation_update_order_related_subscriptions_last_order_date_created' ], 10, 3 );


This function name seems weird to me. Also it does not actually delete the order just update it

@leonardola it deletes the order relation, not the order itself.

leonardola

Under Manual parent ID changes

If the new subscription parent order id is higher than the other others, its date should show up on the Last Order Date column.

Shouldn't it update the latest order every time independently if the new parent is newer or older?

To me even when I update the parent order to a newer one it doesn't update the last order date

Under Add order relation

In this case the newest order was used to calculate the last order date even though I added an older order. In this case I think the current behavior is correct.

leonardola · 2025-04-07T18:42:46Z

includes/class-wc-subscriptions-order.php

+
+		add_action( 'wcs_orders_add_relation', [ __CLASS__, 'add_relation_update_order_related_subscriptions_last_order_date_created' ], 10, 3 );
+
+		add_action( 'wcs_orders_delete_relation', [ __CLASS__, 'delete_relation_update_order_related_subscriptions_last_order_date_created' ], 10, 3 );


This function name seems weird to me. Also it does not actually delete the order just updates it

@leonardola the delete_ is the function names comes from WCS_Related_Order_Store::instance()->delete_relation(, it's not related to delete an order but delete a relation.

leonardola · 2025-04-07T18:51:51Z

includes/class-wc-subscriptions-order.php

+	 * @param WC_Order $subscription  A subscription or order to unlink the order with, if a relation exists.
+	 * @param string   $relation_type The relationship between the subscription and the order. Must be 'renewal', 'switch' or 'resubscribe' unless custom relationships are implemented.
+	 */
+	public static function delete_relation_update_order_related_subscriptions_last_order_date_created( $order, $subscription, $relation_type ) {


This function is also not deleting anything is it?

The function name comes from WCS_Related_Order_Store::instance()->delete_relation(, would you suggest a better name?

leonardola · 2025-04-07T18:54:34Z

includes/wcs-order-functions.php

@@ -277,6 +277,11 @@ function wcs_create_order_from_subscription( $subscription, $type ) {
 			throw new Exception( sprintf( __( 'There was an error fetching the new order (%1$s) for subscription %2$d.', 'woocommerce-subscriptions' ), $type, $subscription->get_id() ) );
 		}

+		// Update the subscription last_order_date_created on every time


make the comment a one liner or change to /* */ style

Fixed on ef28be8.

Cache subscription last order date

2838dff

alefesouza requested a review from mattallan March 21, 2025 01:45

alefesouza added 2 commits March 20, 2025 22:46

Add changelog entry

58b1b27

Fix linting errors

da940a7

mattallan suggested changes Mar 27, 2025

View reviewed changes

leonardola self-requested a review April 4, 2025 19:17

alefesouza added 6 commits April 5, 2025 00:05

Handle parent id changes

7c39ca2

Merge branch 'trunk' into fix/cache-subscription-last-order-date

e910960

Handle parent id null value

e84d69a

Refactoring with Copilot

c0447f4

Fix date update on subscription deletion

205f602

Handle manual relation changes

af8d2c4

leonardola reviewed Apr 7, 2025

View reviewed changes

alefesouza requested a review from leonardola April 8, 2025 00:20

alefesouza added 2 commits April 9, 2025 10:06

Merge branch 'trunk' into fix/cache-subscription-last-order-date

58f14d6

Update comment

e51e8c9

leonardola requested changes Apr 10, 2025

View reviewed changes

alefesouza added 2 commits April 11, 2025 21:44

Fix last order updated on parent id changes

150be3d

Update comment to be single liner

ef28be8

alefesouza requested a review from leonardola April 12, 2025 00:48

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Cache subscription last order date #819

Cache subscription last order date #819

alefesouza commented Mar 21, 2025 •

edited

Loading

mattallan left a comment •

edited

Loading

alefesouza commented Apr 7, 2025

leonardola Apr 7, 2025

alefesouza Apr 8, 2025

leonardola left a comment

leonardola Apr 7, 2025

alefesouza Apr 12, 2025

leonardola Apr 7, 2025

alefesouza Apr 12, 2025

leonardola Apr 7, 2025

alefesouza Apr 12, 2025


		add_action( 'wcs_orders_add_relation', [ __CLASS__, 'add_relation_update_order_related_subscriptions_last_order_date_created' ], 10, 3 );

		add_action( 'wcs_orders_delete_relation', [ __CLASS__, 'delete_relation_update_order_related_subscriptions_last_order_date_created' ], 10, 3 );

Cache subscription last order date #819

Are you sure you want to change the base?

Cache subscription last order date #819

Conversation

alefesouza commented Mar 21, 2025 • edited Loading

Description

How to test this PR

Subscriptions list (existing subscription handling)

Subscription renewal

Subscription order deletion

Manual parent ID changes

Add order relation

Delete order relation

Product impact

mattallan left a comment • edited Loading

Choose a reason for hiding this comment

Parent order changes

Renewal order changes

Switch order changes

alefesouza commented Apr 7, 2025

Choose a reason for hiding this comment

Choose a reason for hiding this comment

leonardola left a comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

alefesouza commented Mar 21, 2025 •

edited

Loading

mattallan left a comment •

edited

Loading