/*** Sort by SKU on admin listing page*/
add_filter( 'request', 'orderby_sku_request_query', 9999 );
function orderby_sku_request_query($query_vars) {
global $typenow;
// remove filter on load if already added otherwise these applied again & again
remove_filter( 'posts_clauses', 'orderby_sku_desc_post_clauses', 9999 );remove_filter( 'posts_clauses', 'orderby_sku_asc_post_clauses', 9999 );
// Custom order by arguments.if ( 'product' === $typenow && isset( $query_vars['orderby'] ) && 'sku' === strtolower( $query_vars['orderby'] ) ) {$order = isset( $query_vars['order'] ) ? strtoupper( $query_vars['order'] ) : 'DESC';$callback = 'DESC' === $order ? 'sort_products_by_sku_desc' : 'sort_products_by_sku_asc';add_filter( 'posts_clauses', $callback, 9999 );}return $query_vars;}
/*** In assending order, keep whitespace in sql query*/function sort_products_by_sku_asc( $args ) {$args['orderby'] = ' CAST(wc_product_meta_lookup.sku AS UNSIGNED) ASC, wc_product_meta_lookup.product_id ASC ';return $args;}/*** In dessending order, keep whitespace in sql query*/function sort_products_by_sku_desc( $args ) {$args['orderby'] = ' CAST(wc_product_meta_lookup.sku AS UNSIGNED) DESC, wc_product_meta_lookup.product_id DESC ';return $args;}
Here's a line-by-line breakdown of how the above code works:
- The code starts by adding a filter to the 'request' hook, which allows you to modify the query variables for the current request.
The function 'orderby_sku_request_query' is called, which removes any existing filters that may interfere with the sorting process.
The function checks if the current post type is 'product', which is the default post type for WooCommerce products.
If the post type is 'product', the function checks if the 'orderby' query variable is set.
If the 'orderby' query variable is set, the function checks if it is set to 'sku', which is the field we want to sort by.
If the 'orderby' query variable is set to 'sku', the function determines whether to sort in ascending or descending order based on the 'order' query variable.
The function then adds a filter to the 'posts_clauses' hook, which allows you to modify the SQL query used to retrieve the posts.
The filter callback function 'orderby_sku_asc_post_clauses' is called if the sort order is ascending, or 'sort_products_by_sku_desc' if the sort order is descending.
The 'sort_products_by_sku_asc' function modifies the 'orderby' argument of the SQL query to sort by the 'sku' field in ascending order, followed by the 'product_id' field in ascending order.
The 'sort_products_by_sku_desc' function modifies the 'orderby' argument of the SQL query to sort by the 'sku' field in descending order, followed by the 'product_id' field in descending order.
The modified SQL query is then used to retrieve the sorted products, which are displayed on the admin listing page.
- Overall, this code provides a simple and efficient way to sort WooCommerce products according to their SKUs, making it easier to manage your online store.