WooCommerce products shortcode order by sku list

Question

Is there a way to modify the WooCommerce Products shortcode to order by a list of product sku's?

A search engine send a Post to the page with a list of sku's. The page should display the Products in the same order the http post is.

Example php and mysql select (the code like this worked in my old shop system where I can use SQL syntax):

$_POST['skus'] = "51,57,34,12,111";
$skus = $_POST['skus'];

select * from products where sku in ('$skus') order by FIELD(sku,'$skus);

How can I do an "orderby" like the example with products shortcode from woocommerce or is there a better way to do that with woocommerce?

Thanks for every answer.

Without testing this, I can't be 100% certain, it may be possible to pass the FIELD(sku, $skus) as the order parameter within the shortcode. i.e. [products skus="foo, bar, baz" orderby="FIELD(sku, 'foo,bar,baz')" order="asc"] — Gavin
I thought it may be a long shot like. Only alternative I can think of then is to manually override the shortcode result and sort the results based on the ids/skus provided. — Gavin
wordpress.stackexchange.com/a/67830/43362 The above looks like a viable solution so long as you're only matching on product id's and not SKU's. — Gavin

Thomas2016 Thomas2016 · Accepted Answer · 2016-05-20T14:36:46

With the help from Gavin and the code from https://wordpress.stackexchange.com/a/67830/43362 I found this solution:

    function wpse67823_orderby_post_in($orderby, $query){

     //Remove it so it doesn't effect future queries
     remove_filter(current_filter(), __FUNCTION__);

     $post__in = $_POST['skus'];

     $post__in = str_replace(',','\',\'',$post__in);

     return "FIELD(CAST(mt2.meta_value AS CHAR), '$post__in' )";
     } 

    //Add filter and perform query
    add_filter('posts_orderby','wpse67823_orderby_post_in',10,2);

    echo do_shortcode('[products skus="'.$skus.'" orderby="sku"]');

    remove_filter('posts_orderby','wpse67823_orderby_post_in',10,2);

WooCommerce products shortcode order by sku list

1 Answers