Default SKU Sorting for WooCommerce Wholesale Order Form

functions.php

/**
 * Default SKU Sorting for WooCommerce Wholesale Order Form
 * 
 * This snippet sets the default sorting to SKU (lowest to highest)
 * when the order form is first loaded (no search, no explicit sorting).
 * 
 * Copy this entire code into Code Snippets plugin
 */

add_filter( 'wwof_posts_clauses_request', 'wwof_default_sku_sorting', 10, 2 );

/**
 * Set default sorting by SKU (ascending) when no search or explicit ordering is set
 * 
 * @param array $clauses Query clauses
 * @param object $wc_instance WC integration instance
 * @return array Modified query clauses
 */
function wwof_default_sku_sorting( $clauses, $wc_instance ) {
    global $wpdb;
    
    // Only apply to REST requests for the order form
    if ( ! defined( 'REST_REQUEST' ) || ! is_a( $wc_instance->rest_request, 'WP_REST_Request' ) ) {
        return $clauses;
    }
    
    // Check if there's a search term - if yes, don't override (let search relevance handle it)
    $search_term = $wc_instance->rest_request->get_param( 'search' );
    if ( ! empty( $search_term ) ) {
        return $clauses;
    }
    
    // Check if there's an explicit orderby parameter
    $order_by = sanitize_text_field( $wc_instance->rest_request->get_param( 'orderby' ) );
    if ( ! empty( $order_by ) ) {
        return $clauses; // Don't override explicit sorting
    }
    
    // Check if we're in a search context (SKU search active)
    if ( ! empty( $wc_instance->search_sku_in_product_lookup_table ) ) {
        return $clauses; // Let search relevance handle it
    }
    
    // Ensure the wc_product_meta_lookup table is joined
    if ( strpos( $clauses['join'], 'wc_product_meta_lookup' ) === false ) {
        $clauses['join'] = "LEFT JOIN {$wpdb->prefix}wc_product_meta_lookup wc_product_meta_lookup ON {$wpdb->posts}.ID = wc_product_meta_lookup.product_id " . $clauses['join'];
    }
    
    // Check if there are priority products or favorites that should come first
    // We'll preserve those if they exist, then add SKU sorting
    $has_priority_or_favorites = false;
    
    // Check if orderby already contains FIELD() which is used for priority/favorites
    if ( ! empty( $clauses['orderby'] ) && strpos( $clauses['orderby'], 'FIELD(' ) !== false ) {
        $has_priority_or_favorites = true;
    }
    
    // Build SKU ordering
    // Sort numeric SKUs numerically, alphanumeric SKUs alphabetically
    // This ensures "101" comes before "200", and handles both numeric and alphanumeric SKUs
    $sku_order = "
        CASE 
            WHEN wc_product_meta_lookup.sku REGEXP '^[0-9]+$' 
            THEN CAST(wc_product_meta_lookup.sku AS UNSIGNED)
            ELSE 999999999
        END ASC,
        LENGTH(wc_product_meta_lookup.sku) ASC,
        wc_product_meta_lookup.sku ASC";
    
    if ( $has_priority_or_favorites ) {
        // If priority/favorites exist, add SKU sorting after them
        $clauses['orderby'] = $clauses['orderby'] . ', ' . $sku_order;
    } else {
        // Replace default ordering with SKU ordering
        // Check if there's existing orderby
        if ( ! empty( $clauses['orderby'] ) ) {
            // Try to replace post_date ordering with SKU ordering (like the original code does)
            $order = 'ASC'; // Default order
            $replaced = str_replace(
                "{$wpdb->posts}.post_date $order",
                trim( $sku_order ) . ", {$wpdb->posts}.post_title $order",
                $clauses['orderby']
            );
            
            // If replacement worked, use it; otherwise prepend SKU ordering
            if ( $replaced !== $clauses['orderby'] ) {
                $clauses['orderby'] = $replaced;
            } elseif ( strpos( $clauses['orderby'], 'wc_product_meta_lookup.sku' ) === false ) {
                // Prepend SKU ordering if it's not already there
                $clauses['orderby'] = trim( $sku_order ) . ', ' . $clauses['orderby'];
            }
        } else {
            // No existing orderby, set SKU ordering
            $clauses['orderby'] = trim( $sku_order );
        }
    }
    
    return $clauses;
}