search – Refining Sort by SKU
I’m looking for a way to sort products by their SKU while ignoring their prefix. The SKUs of my products have an identical prefix and a 5 digit number (eg BM12345) however some of the older products have a four digit SKU (eg BM1234). I’ve used the example code from https://gist.github.com/bekarice/1883b7e678ec89cc8f4d as a guide to do the sorting, and all the 4 digit results are listed first –
function sv_add_sku_sorting( $args ) {
$orderby_value = isset( $_GET['orderby'] ) ? wc_clean( $_GET['orderby'] ) : apply_filters( 'woocommerce_default_catalog_orderby', get_option( 'woocommerce_default_catalog_orderby' ) );
if ( 'sku' == $orderby_value ) {
$args['orderby'] = 'LENGTH(meta_value) DESC, meta_value ASC';
$args['orderby'] = "meta_value";
$args['order'] = 'desc'; // lists SKUs alphabetically 0-9, a-z; change to desc for reverse alphabetical
$args['meta_key'] = '_sku';
}
return $args;
}
add_filter( 'woocommerce_get_catalog_ordering_args', 'sv_add_sku_sorting' );
/**
* Add the option to the orderby dropdown.
*
* @param array $sortby the sortby options
* @return array updated sortby
*/
function sv_sku_sorting_orderby( $sortby ) {
// Change text above as desired; this shows in the sorting dropdown
$sortby['sku'] = __( 'Sort by SKU', 'textdomain' );
return $sortby;
}
add_filter( 'woocommerce_catalog_orderby', 'sv_sku_sorting_orderby' );
add_filter( 'woocommerce_default_catalog_orderby_options', 'sv_sku_sorting_orderby' );
Is there a way to strip the leading characters by passing another argument that will then get processed as SQL or am I on the wrong track?
eg – I tried $args['orderby'] = 'LENGTH(meta_value) DESC, meta_value DESC';
but that didn’t work.
Thank you in advance!
Leave an answer