Sort specific Woocommerce product variations by menu_order in a WP_Query

Question

I have a WP_Query to pull and display a list of WooCommerce Product variations filtered by global attributes. This works, but I would like to custom sort the results by ‘menu_order‘.

But this doesn't work for 'post_type' => 'product_variation'. Probably because ‘menu_order‘ is set on a product level. ('post_type' => 'product' does work but I need the variations)

$wc_query_params = array(
     'post_type' => 'product_variation',
     'posts_per_page' => -1,
     'numberposts'   => -1,
     'post_status'   => 'publish',
     'meta_query'     => array(array(
         //filter variations on attribute name
         'key'  => 'attribute_pa_color',
         'value'     => 'blue', 
     )),
     'order'         => 'ASC',
     'orderby'       => 'menu_order',
 );
 $wc_query = new WP_Query($wc_query_params);

 if ($wc_query->have_posts()) :
     while ($wc_query->have_posts()) :
         $wc_query->the_post();

     //results

     endwhile;
     wp_reset_postdata();
 endif;

Is there a way to sort product variations based on their parent product ‘menu_order‘? Or is there another way to custom sort product variations?

Answer 1

Using the answer from this question. I managed to display product variations in the same menu_order as their parent variable products.

Note: This code also filters the results by product category (which I needed).

In the function below I added ORDER by p.menu_order to sort the variation parent ids by menu_order.

function get_variation_parent_ids_from_term( $term, $taxonomy, $type ){
global $wpdb;

return $wpdb->get_col( "
    SELECT DISTINCT p.ID
    FROM {$wpdb->prefix}posts as p
    INNER JOIN {$wpdb->prefix}posts as p2 ON p2.post_parent = p.ID
    INNER JOIN {$wpdb->prefix}term_relationships as tr ON p.ID = tr.object_id
    INNER JOIN {$wpdb->prefix}term_taxonomy as tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
    INNER JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
    WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND p2.post_status = 'publish'
    AND tt.taxonomy = '$taxonomy'
    AND t.$type = '$term'
    ORDER by p.menu_order 
" );
}

And I used this sorting order to sort the product_variations by their parent products. Using 'orderby' => 'post_parent__in' to use the same order given in post_parent__in.

$cat_name = 't-shirt';  // Product category name

$wc_query = new WP_Query( array(
    'post_type'      => 'product_variation',
    'posts_per_page' => -1,
    'numberposts'    => -1,
    'post_status'    => 'publish',
    'meta_query'     => array( array(
        'key'   => 'attribute_pa_color',
        'value' => 'blue', 
    ) ),
    'post_parent__in' => get_variation_parent_ids_from_term( $cat_name, 'product_cat', 'name' ), 
    'orderby'         => 'post_parent__in', //orderby = same order as their parent products
) );

Might be useful for others.