'How to optimize slowly custom WP Query for Product Bundles?

I am trying to troubleshoot my WP Query for the second day and I am getting tired and need help. I managed to make it work, but very slow (needs 5s to load instead of about 0.5s).

My problem: I need to move the unavailable products to the end of WP Query, and sort the rest by sorting in the "menu_order" panel.

I'm using the Product Bundles plugin, which doesn't use "_stock_status" but has its own meta_key "_wc_pb_bundled_items_stock_status", which for 1/4 of the sets is populated with "instock" and "outofstock" values. I have no idea why this is not the case with 100% of the products. When a product becomes unavailable, it has "_stock_status" = instock, but an additional "_wc_pb_bundled_items_stock_status" = outofstock appears.

I guess if this worked 100% smoothly, I could just sort the "_wc_pb_bundled_items_stock_status" meta_key ascendingly and additionally use "menu_order" sorting.

My code:

<?php

 $paged = get_query_var( 'paged' ) ? get_query_var( 'paged' ) : 1;
 $args = array(
     'post_type' => 'product',
     'posts_per_page' => 18,
     'paged' => $paged,
     'product_cat' => 'zestawy',
     'meta_query'  => array(
        'relation' => 'OR',
        'instock_default_order' => array(
            'relation' => 'OR',
            array(
                'key'     => '_wc_pb_bundled_items_stock_status',
                'value'   => 'instock',
                'compare' => '=',
            ),
            array(
                'key'     => '_stock_status',
                'value' => 'instock', 
                'compare' => '=',
            ),
        ),
        'outofstock_order' => array(
            array(
                'key'     => '_wc_pb_bundled_items_stock_status',
                'value'   => 'outofstock',
                'compare' => '=',
            ),
        )
    ),
    'orderby' => array(
        '_wc_pb_bundled_items_stock_status' => 'DESC',
        'menu_order' => 'ASC',
    ),
);

$wp_query = new WP_Query( $args );

?>

I started reading about how to optimize this code and used the "pre_get_posts" function. This reduced the page load time from 5s to 4s, but the effect is still poor. After many attempts and changes I managed to write this code:

function instock_bundle_query($query){
    if ( is_product_category('zestawy') && $query->is_main_query() ) {

        $query->set('meta_query', array(

            'instock_default_order' => array(
                'relation' => 'OR',
                array (      
                    'key'     => '_wc_pb_bundled_items_stock_status',
                    'compare' => 'NOT EXISTS',
                ),
                array (      
                    'key'     => '_wc_pb_bundled_items_stock_status',
                    'compare' => 'EXISTS',
                ),
            )
            ));

        $query->set('orderby',array(
            'meta_value' => 'ASC',
            'menu_order' => 'ASC'
        )); 
    }};

 add_action( 'pre_get_posts', 'instock_bundle_query' );

Which works much faster, but does not work properly. Unavailable products are at the end of the list, but bundles that do not have meta_key "_wc_pb_bundled_items_stock_status" are displayed first, followed by those that have the meta_key "instock". Is there any way to improve this?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source