SKU Search Relevance Fix for WooCommerce Wholesale Order Form

functions.php

/**
 * SKU Search Relevance Fix for WooCommerce Wholesale Order Form
 * 
 * This snippet fixes the SKU search to prioritize exact matches first,
 * then SKUs starting with the search term, then partial matches.
 * 
 * Copy this entire code into Code Snippets plugin
 */

add_filter( 'wwof_search_post_clauses_request', 'wwof_sku_search_relevance_ordering', 10, 2 );

/**
 * Add relevance-based ordering to SKU search results
 * 
 * @param array $clauses Query clauses
 * @param object $wc_instance WC integration instance
 * @return array Modified query clauses
 */
function wwof_sku_search_relevance_ordering( $clauses, $wc_instance ) {
    global $wpdb;
    
    // Get the search term from the REST request
    if ( ! is_a( $wc_instance->rest_request, 'WP_REST_Request' ) ) {
        return $clauses;
    }
    
    $search_term = $wc_instance->rest_request->get_param( 'search' );
    
    // Only apply if there's a search term and SKU search is enabled
    if ( empty( $search_term ) || empty( $wc_instance->search_sku_in_product_lookup_table ) ) {
        return $clauses;
    }
    
    // Check if we're already ordering by SKU explicitly
    $order_by = sanitize_text_field( $wc_instance->rest_request->get_param( 'orderby' ) );
    
    // Escape the search term for SQL
    $escaped_search = $wpdb->esc_like( $search_term );
    
    // Build relevance-based ORDER BY clause
    // Priority 1: Exact SKU matches (sku = '200')
    // Priority 2: SKUs starting with search term (sku LIKE '200%')
    // Priority 3: SKUs containing search term (sku LIKE '%200%')
    // Priority 4: Alphabetical by SKU
    $relevance_order = $wpdb->prepare(
        "CASE 
            WHEN wc_product_meta_lookup.sku = %s THEN 0
            WHEN wc_product_meta_lookup.sku LIKE %s THEN 1
            WHEN wc_product_meta_lookup.sku LIKE %s THEN 2
            ELSE 3
        END ASC,
        wc_product_meta_lookup.sku ASC",
        $search_term,                                    // Exact match
        $escaped_search . '%',                          // Starts with
        '%' . $escaped_search . '%'                     // Contains
    );
    
    // If there's already an orderby clause, prepend our relevance ordering
    if ( ! empty( $clauses['orderby'] ) ) {
        // Check if it's already ordering by SKU
        if ( '_sku' === $order_by ) {
            // Replace the existing SKU ordering with relevance-based ordering
            $clauses['orderby'] = preg_replace(
                '/wc_product_meta_lookup\.sku\s+(ASC|DESC)/i',
                $relevance_order,
                $clauses['orderby']
            );
        } else {
            // Prepend relevance ordering before existing orderby
            $clauses['orderby'] = $relevance_order . ', ' . $clauses['orderby'];
        }
    } else {
        // No existing orderby, set our relevance ordering
        $clauses['orderby'] = $relevance_order;
    }
    
    return $clauses;
}