Sequential Order Numbers Pro for WooCommerce
by
SkyVerge
Tame your order numbers! Upgrade from Sequential Order Numbers with advanced features and with optional prefixes/suffixes.
Ability for admin to search orders by Order Number (selected in dropdown) when using HPOS.
With HPOS order storage, WooCommerce have added a dropdown selector to filter what the search looks for.
Default options are: ‘All’, ‘Order ID’, ‘Customer Email Address’, ‘Customers’ and ‘Products’.
Search for an order number does not return any results if ‘Order ID’ is selected.
If ‘All’ is selected the search is very very slow to return results.
Whenever Sequential Order Numbers Pro is active, I’d like to propose either adding ‘Order Number’ to the dropdown, or modyfying it so that the ‘Order ID’ field will also accept order number.
Thanks
Screenshots
Author
adminflowerpower
Current Status
Open
Last updated: July 8, 2024
1 comment
Log in to comment on this feature request.
I had the same problem and created a solution that makes the ‘Order ID’ search look for Sequential Order Numbers in the WooCommerce order search of the backend with HPOS activated.
Add this to your functions.php in the (child)theme directory or via the Code Snippets plugin:
/**
* Replace Order ID search with Sequential Order Number functionality in WooCommerce HPOS
*/
// Modify the search filters to use Sequential Number search instead of Order ID
add_filter( ‘woocommerce_hpos_admin_search_filters’, function( $options ) {
// Keep the original ‘order_id’ key but modify the label
$options[‘order_id’] = __( ‘Order Number’, ‘woocommerce’ );
return $options;
});
// Implement search functionality for Order Numbers
add_filter( ‘woocommerce_hpos_generate_where_for_search_filter’, function( $where, $search_term, $filter_type ) {
global $wpdb;
if ( ‘order_id’ === $filter_type ) { // Note: we use ‘order_id’ as filter_type
$meta_table = $wpdb->prefix . ‘wc_orders_meta’;
$orders_table = $wpdb->prefix . ‘wc_orders’;
// Optimized query without database modifications
$where = $wpdb->prepare(
“EXISTS (
SELECT 1
FROM {$meta_table}
WHERE {$meta_table}.order_id = {$orders_table}.id
AND {$meta_table}.meta_key = ‘_order_number_formatted’
AND {$meta_table}.meta_value = %s
)”,
$search_term // Exact match for better performance
);
// If no exact match, try LIKE search
if ($wpdb->get_var($wpdb->prepare(
“SELECT COUNT(*) FROM {$meta_table} WHERE meta_key = ‘_order_number_formatted’ AND meta_value = %s”,
$search_term
)) == 0) {
$where = $wpdb->prepare(
“EXISTS (
SELECT 1
FROM {$meta_table}
WHERE {$meta_table}.order_id = {$orders_table}.id
AND {$meta_table}.meta_key = ‘_order_number_formatted’
AND {$meta_table}.meta_value LIKE %s
)”,
$wpdb->esc_like( $search_term ) . ‘%’ // Only suffix wildcard
);
}
}
return $where;
}, 10, 3 );