Sort WooCommerce Products by SKU Numeric (1.2.3.4.5.6........)

Question

In WooCommerce Version 4.2.2 on admin products list, when I try to sort products by SKU (column), the result is: 1 10 100 101 102 103 104 ...
InsteadI would like it to be in natural order like: 1 2 3 4 5 ... 9 10 11 ... 99 ... 100 101 ...

The same thing happens when I use WooCommerce Product Search (Version 2.21.0).

Trying to understand what is happening I noticed that if I do a search in the admin panel for products it returns too many results and this does not happen to all products, some work and it only brings me 1 single product, others bring more.

I tried to reinstall all Wordpress, WooCommerce and WooCommerce product search, without any change. I tested other search plugins with the same results.

Any help please?

I looked in mysql and the field is varchar type I tried to change it in int field and it still doesn't work — Toma Eugen, Jun 26 '20 at 07:53

LoicTheAztec · Answer 1 · 2020-06-26T15:58:46.753

The rule in StackOverFlow is one question at the time… So I am answering for admin products list.

The following hooked function will allow to sort products by numerical skus in a natural sorting order altering the ORDER BY in the related SQL query using ABS():

add_filter( 'posts_clauses', 'admin_products_list_orderby_sku_as_int', 999, 1 );
function admin_products_list_orderby_sku_as_int( $posts_clauses ){
    global $pagenow, $typenow, $wp_query;

    $query_vars = $wp_query->query_vars;

    if ( $pagenow == 'edit.php' && 'product' === $typenow && isset( $_GET['post_type'] )
    && isset( $query_vars['orderby'] ) && 'sku' === $query_vars['orderby'] ) {
        $orderby   = strtolower( $query_vars['orderby'] );
        $order     = isset( $query_vars['order'] ) ? strtoupper( $query_vars['order'] ) : 'DESC';

        if ( 'DESC' === $order ) {
            $posts_clauses['orderby'] = ' ABS( wc_product_meta_lookup.sku ) DESC, wc_product_meta_lookup.sku DESC, wc_product_meta_lookup.product_id DESC ';
        } else {
            $posts_clauses['orderby'] = ' ABS( wc_product_meta_lookup.sku ) ASC, wc_product_meta_lookup.sku ASC, wc_product_meta_lookup.product_id ASC ';
        }
    }
    return $posts_clauses;
}

Code goes in functions.php file of your active child theme (or active theme). Tested and works.

See: SQL ORDER chars numerically

Sort WooCommerce Products by SKU Numeric (1.2.3.4.5.6........)

1 Answers1

Linked