[Resolved] Woo archive secondary sorting by custom field

[Author]

Posts

[tomasP]

Hello,
as Secondary sorting for Product archive template is still not functional after three years (https://toolset.com/errata/woocommerce-product-archive-sorting-broken-whenever-secondary-sorting-added/) I am forced to kindly ask for support.

I have a Woo product archive template, sorted by custom field "Hmotnost" (weight). But as I have lots of products with same weight, I need secondary sorting by SKU to keep products sorting predictably, not randomly (therefore I need this secondary sorting).

Could you help, please?
Regards,
Tom

[Minesh]

Hello. Thank you for contacting the Toolset support.

I checked our internal ticket and ther eis no word done on this. I've added your voice to our internal ticket so it will get attention.

However, what if you try to add the following workaround I shared using "pre_get_posts" hook to "Custom Code" section offered by Toolset:

function func_custom_sort_products_by_weight_and_sku( $query ) {
    if ( ! is_admin() && $query->is_main_query() && is_post_type_archive( 'product' ) ) {
        
        $query->set( 'meta_query', array(
            'relation' => 'AND',
            'weight_clause' => array(
                'key'     => 'wpcf-weight',
                'type'    => 'NUMERIC',
            ),
            'sku_clause' => array(
                'key'     => '_sku',
            ),
        ));

        $query->set( 'orderby', array(
            'weight_clause' => 'ASC',   // Primary: custom field "weight"
            'sku_clause'    => 'ASC',   // Secondary: product SKU
        ));
    }
}
add_action( 'pre_get_posts', 'func_custom_sort_products_by_weight_and_sku',99,1 );

You can change the order ASC/DESC as required

[tomasP]

Hello, Minesh,
thanks very much for your response, however, it still doesn't work as expected.
The primary sorting works fine (field wpcf-produkt-hmotnost), but the secondary sorting is still random - each time I reload the page, different order of results is displayed.
Tried to put the code first to functions.php of my child theme (Generate Press), then to Custom code snippets and same results in both cases. Shouldn't I change something in the archive template as well? I am using a select to sort results on the frontend - (weight and price - the weight/Hmotnost should be default sorting)
Thanks very much for your time
Tom

[Minesh]

Ok, you have sorting controls added to your view.

But I belive it will give you on sorting at a time. If you select "weight", it should sort by weight, if you select "SKU", it should sort result by SKU. Is this you want - I man as per the user's selected choice the sorting should be performed?

In addition to that - Can you please share problem URL of your archive as well as admin access details and tell me what is your expected results and how exactly you want to sort the product posts.

*** Please make a FULL BACKUP of your database and website.***
I would also eventually need to request temporary access (WP-Admin and FTP) to your site. Preferably to a test site where the problem has been replicated if possible in order to be of better help and check if some configurations might need to be changed.

I have set the next reply to private which means only you and I have access to it.

[Minesh]

Can you please check now.

I've adjusted the code added to "Custom Code" section as given under:

// Put the code of your snippet below this comment.
function func_custom_sort_products_by_weight_and_sku( $query ) {
    if ( ! is_admin() && $query->is_main_query() && is_post_type_archive( 'product' ) ) {
      
        $direction = ( isset($_REQUEST['wpv_sort_order']) && strtoupper($_REQUEST['wpv_sort_order']) == 'DESC' ) ? 'DESC' : 'ASC';
      
       $sort = isset( $_REQUEST['wpv_sort_orderby'] ) 
              ? sanitize_text_field( $_REQUEST['wpv_sort_orderby'] ) 
              : '';
      
      	$sort_field = 'wpcf-produkt-hmotnost';
      	$meta_query = array(
                                'relation' => 'AND',
                                'primary_clause' => array(
                                    'key'     => $sort_field,
                                    'type'    => 'NUMERIC',
                                ),
                                'sku_clause' => array(
                                    'key'     => '_sku',
                                ),
                            );
      
      	$order_by = array('primary_clause' => $direction,  // Primary: custom field"weight"
            'sku_clause'    => 'ASC',   // Secondary: product SKU
        );
      
      	if(!empty($sort)){
          	$sort = $_REQUEST['wpv_sort_orderby'];
          	if($sort == 'field-wpcf-produkt-hmotnost'){
              $sort_field = 'wpcf-produkt-hmotnost';
             
              
            }else if($sort == 'price'){
              	 $sort_field = '_price';
              	 $meta_query = array(
                                'relation' => 'AND',
                                'primary_clause' => array(
                                    'key'     => $sort_field,
                                    'type'    => 'NUMERIC',
                                ),
                                'sku_clause' => array(
                                    'key'     => '_sku',
                                ),
                            );
              	
            }
        
        }
         
        $query->set( 'meta_query', $meta_query);
        $query->set( 'orderby',$order_by );
        
    }
}
add_action( 'pre_get_posts', 'func_custom_sort_products_by_weight_and_sku',999,1 );

Can you please confirm it works as expected.

[tomasP]

Hello, Minesh, first of all - thank you!
But there is a small "but" - it works almost as expected 🙂
Upon entry to the shop page, it orders the products by weight and then SKU, but it starts with weight "1.2441 g", completely ignoring products with "1 g" weight, which are ordered just after all weights with weight fraction starting with "1".
I think it is because of the delimiter, is that correct? I think I would be able to change the delimiter from "." to "," which is default to the rest of the web. As this is not a piece of cake operation in my situation - do you think this might cause the problem? If you think it is, I will change it, no problem 🙂 I just want to avoid not necessary work in case I am mistaken 🙂
Thank you VERY much!

[Minesh]

Can you please check now: hidden link

I've adjusted the code added to "Custom Code" section as given under:

/ Put the code of your snippet below this comment.
function func_custom_sort_products_by_weight_and_sku( $query ) {
    if ( ! is_admin() && $query->is_main_query() && is_post_type_archive( 'product' ) ) {
       
      	
       //$query->query_vars['meta_key']='';
      
        $direction = ( isset($_REQUEST['wpv_sort_order']) && strtoupper($_REQUEST['wpv_sort_order']) == 'DESC' ) ? 'DESC' : 'ASC';
       
       $sort = isset( $_REQUEST['wpv_sort_orderby'] ) 
              ? sanitize_text_field( $_REQUEST['wpv_sort_orderby'] ) 
              : '';
       
        $sort_field = 'wpcf-produkt-hmotnost';
        
       
        $order_by = array('primary_clause' => $direction,  // Primary: custom field"weight"
            'sku_clause'    => 'ASC',   // Secondary: product SKU
        );
       
        if(!empty($sort)){
            $sort = $_REQUEST['wpv_sort_orderby'];
            if($sort == 'field-wpcf-produkt-hmotnost'){
              $sort_field = 'wpcf-produkt-hmotnost';
              
               
            }else if($sort == 'price'){
                 $sort_field = '_price';
                                 
            }
          
          
         
        }
      
      $meta_query = array(
                                'relation' => 'AND',
                                'primary_clause' => array(
                                    'key'     => $sort_field,
                                    'type'    => 'NUMERIC',
                                  	'compare' => 'EXISTS',
                                ),
                                'sku_clause' => array(
                                    'key'     => '_sku',
                                  	'compare' => 'EXISTS',
                                ),
                            );
          
        $query->set( 'meta_query', $meta_query);
        $query->set( 'orderby',$order_by );
         
    }
}
add_action( 'pre_get_posts', 'func_custom_sort_products_by_weight_and_sku',99,1 );

[tomasP]

Sorry, now it displays/orders the weight correctly, but the secondary sorting (SKU) is not working - the results are chaotic once again as in the start. The previous code was closer to desired 🙂 I don't want to waste your time - was the problem in the decimal delimiter? I can change it from "." to ","

[Minesh]

I'm not sure otherwise code is correct to set order by result per weight and sku.

Maybe you can check with SQL query expert based on your content and check why you do not get the desired results. The result is not displaying as expected only when the SKU as secondary sortin applied.

[tomasP]

Ok, the second last solution if close enough for me, thanks VERY much for your patience and effort! Very much appreciated!
All the best,
Tom