Commit 1c4581fba7 for woocommerce
commit 1c4581fba7b42376e43bfc68033b1f2e6b5ba142
Author: Christopher Allford <6451942+ObliviousHarmony@users.noreply.github.com>
Date: Thu Nov 27 21:54:39 2025 -0800
Rely on Case-Insensitive Collation for Coupon Lookups (#62145)
There's no good reason to `LOWER()` the
coupon code when checking. The collation
is virtually guaranteed to be case-insensitive.
diff --git a/plugins/woocommerce/changelog/62145-tweak-60475-optimize-coupon-lookup-query b/plugins/woocommerce/changelog/62145-tweak-60475-optimize-coupon-lookup-query
new file mode 100644
index 0000000000..4c436ef118
--- /dev/null
+++ b/plugins/woocommerce/changelog/62145-tweak-60475-optimize-coupon-lookup-query
@@ -0,0 +1,4 @@
+Significance: minor
+Type: performance
+
+Rely on case-insensitive collation when looking coupon codes up.
\ No newline at end of file
diff --git a/plugins/woocommerce/includes/admin/meta-boxes/views/html-order-items.php b/plugins/woocommerce/includes/admin/meta-boxes/views/html-order-items.php
index a24678acda..9ae68c59d2 100644
--- a/plugins/woocommerce/includes/admin/meta-boxes/views/html-order-items.php
+++ b/plugins/woocommerce/includes/admin/meta-boxes/views/html-order-items.php
@@ -130,7 +130,7 @@ if ( wc_tax_enabled() ) {
$coupon_info = json_decode( $coupon_info, true );
$post_id = $coupon_info[0]; //phpcs:ignore WordPress.WP.GlobalVariablesOverride.Prohibited
} else {
- $post_id = $wpdb->get_var( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE LOWER(post_title) = LOWER(%s) AND post_type = 'shop_coupon' AND post_status = 'publish' AND post_date < %s LIMIT 1;", wc_sanitize_coupon_code( $item->get_code() ), $order->get_date_created()->format( 'Y-m-d H:i:s' ) ) ); // phpcs:disable WordPress.WP.GlobalVariablesOverride.Prohibited
+ $post_id = $wpdb->get_var( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title = %s AND post_type = 'shop_coupon' AND post_status = 'publish' AND post_date < %s LIMIT 1;", wc_sanitize_coupon_code( $item->get_code() ), $order->get_date_created()->format( 'Y-m-d H:i:s' ) ) ); // phpcs:disable WordPress.WP.GlobalVariablesOverride.Prohibited
}
$class = $order->is_editable() ? 'code editable' : 'code';
?>
diff --git a/plugins/woocommerce/includes/data-stores/class-wc-coupon-data-store-cpt.php b/plugins/woocommerce/includes/data-stores/class-wc-coupon-data-store-cpt.php
index cb24b422a1..6b92fb7c0a 100644
--- a/plugins/woocommerce/includes/data-stores/class-wc-coupon-data-store-cpt.php
+++ b/plugins/woocommerce/includes/data-stores/class-wc-coupon-data-store-cpt.php
@@ -213,7 +213,7 @@ class WC_Coupon_Data_Store_CPT extends WC_Data_Store_WP implements WC_Coupon_Dat
// The `coupon_id_from_code` entry in the object cache must not exist when the coupon is not published, otherwise the coupon will remain available for use.
if ( 'publish' !== $coupon->get_status() ) {
- $hashed_code = md5( $coupon->get_code() );
+ $hashed_code = md5( wc_strtolower( $coupon->get_code() ) );
wp_cache_delete( WC_Cache_Helper::get_cache_prefix( 'coupons' ) . 'coupon_id_from_code_' . $hashed_code, 'coupons' );
}
@@ -245,7 +245,7 @@ class WC_Coupon_Data_Store_CPT extends WC_Data_Store_WP implements WC_Coupon_Dat
if ( $args['force_delete'] ) {
wp_delete_post( $id );
- $hashed_code = md5( $coupon->get_code() );
+ $hashed_code = md5( wc_strtolower( $coupon->get_code() ) );
wp_cache_delete( WC_Cache_Helper::get_cache_prefix( 'coupons' ) . 'coupon_id_from_code_' . $hashed_code, 'coupons' );
$coupon->set_id( 0 );
@@ -789,7 +789,7 @@ class WC_Coupon_Data_Store_CPT extends WC_Data_Store_WP implements WC_Coupon_Dat
global $wpdb;
return $wpdb->get_col(
$wpdb->prepare(
- "SELECT ID FROM $wpdb->posts WHERE LOWER(post_title) = LOWER(%s) AND post_type = 'shop_coupon' AND post_status = 'publish' ORDER BY post_date DESC",
+ "SELECT ID FROM $wpdb->posts WHERE post_title = %s AND post_type = 'shop_coupon' AND post_status = 'publish' ORDER BY post_date DESC",
wc_sanitize_coupon_code( $code )
)
);
diff --git a/plugins/woocommerce/includes/wc-coupon-functions.php b/plugins/woocommerce/includes/wc-coupon-functions.php
index 30a3f6b479..0b6e3e3654 100644
--- a/plugins/woocommerce/includes/wc-coupon-functions.php
+++ b/plugins/woocommerce/includes/wc-coupon-functions.php
@@ -114,7 +114,7 @@ function wc_get_coupon_id_by_code( $code, $exclude = 0 ) {
$data_store = WC_Data_Store::load( 'coupon' );
// Coupon code allows spaces, which doesn't work well with some cache engines (e.g. memcached).
- $hashed_code = md5( $code );
+ $hashed_code = md5( wc_strtolower( $code ) );
$cache_key = WC_Cache_Helper::get_cache_prefix( 'coupons' ) . 'coupon_id_from_code_' . $hashed_code;
$ids = wp_cache_get( $cache_key, 'coupons' );
diff --git a/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-coupon.php b/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-coupon.php
index 15d5f40374..f1b3c5a604 100644
--- a/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-coupon.php
+++ b/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-coupon.php
@@ -56,7 +56,7 @@ class WC_Helper_Coupon {
update_post_meta( $coupon_id, $key, $value );
}
- return new WC_Coupon( $coupon_code );
+ return new WC_Coupon( $coupon_id );
}
/**
@@ -67,9 +67,8 @@ class WC_Helper_Coupon {
* @return bool
*/
public static function delete_coupon( $coupon_id ) {
- wp_delete_post( $coupon_id, true );
-
- return true;
+ $coupon = new WC_Coupon( $coupon_id );
+ return $coupon->delete( true );
}
/**
diff --git a/plugins/woocommerce/tests/legacy/unit-tests/coupon/data-store.php b/plugins/woocommerce/tests/legacy/unit-tests/coupon/data-store.php
index 6e0d519089..a9a4be6579 100644
--- a/plugins/woocommerce/tests/legacy/unit-tests/coupon/data-store.php
+++ b/plugins/woocommerce/tests/legacy/unit-tests/coupon/data-store.php
@@ -56,10 +56,19 @@ class WC_Tests_Coupon_Data_Store extends WC_Unit_Test_Case {
*/
public function test_coupon_cache_deletion() {
$coupon = WC_Helper_Coupon::create_coupon( 'test' );
+
+ // Prime the cache.
+ $hashed_code = md5( wc_strtolower( $coupon->get_code() ) );
+ $cache_name = WC_Cache_Helper::get_cache_prefix( 'coupons' ) . 'coupon_id_from_code_' . $hashed_code;
+ wc_get_coupon_id_by_code( $coupon->get_code() );
+
+ $ids = wp_cache_get( $cache_name, 'coupons' );
+
+ $this->assertNotEquals( false, $ids, sprintf( 'Object cache for %s was not primed correctly.', $cache_name ) );
+
$coupon->delete( true );
- $cache_name = WC_Cache_Helper::get_cache_prefix( 'coupons' ) . 'coupon_id_from_code_' . $coupon->get_code();
- $ids = wp_cache_get( $cache_name, 'coupons' );
+ $ids = wp_cache_get( $cache_name, 'coupons' );
$this->assertEquals( false, $ids, sprintf( 'Object cache for %s was not removed upon deletion of coupon.', $cache_name ) );
}
diff --git a/plugins/woocommerce/tests/legacy/unit-tests/coupon/functions.php b/plugins/woocommerce/tests/legacy/unit-tests/coupon/functions.php
index 69352f46d9..d7f266e27b 100644
--- a/plugins/woocommerce/tests/legacy/unit-tests/coupon/functions.php
+++ b/plugins/woocommerce/tests/legacy/unit-tests/coupon/functions.php
@@ -80,4 +80,24 @@ class WC_Tests_Functions extends WC_Unit_Test_Case {
$this->assertEmpty( wc_get_coupon_id_by_code( 0 ) );
}
+ /**
+ * Test wc_get_coupon_id_by_code().
+ *
+ * @since 10.5
+ */
+ public function test_wc_get_coupon_id_by_code_case_insensitive() {
+ // Create two coupons with the same code in a different case.
+ $coupon1 = WC_Helper_Coupon::create_coupon( 'testcoupon' );
+ $coupon2 = WC_Helper_Coupon::create_coupon( 'TESTCOUPON' );
+
+ // Expect the second coupon to be returned as it was created last.
+ $this->assertEquals( $coupon2->get_id(), wc_get_coupon_id_by_code( 'testCOUPON' ) );
+
+ // Delete second coupon.
+ WC_Helper_Coupon::delete_coupon( $coupon2->get_id() );
+
+ $this->assertEquals( $coupon1->get_id(), wc_get_coupon_id_by_code( 'testCOUPON' ) );
+
+ WC_Helper_Coupon::delete_coupon( $coupon1->get_id() );
+ }
}