Add search form to WooCommerce My Account > Orders page to query specific order data

Question

Having to go "back to the drawing board" as my previous question, although worked, it only worked because of the ['post__in'] being the order ID.

Objective: add a frontend search form for customer to search orders their own orders on the My Account > Orders page.

This shop has a couple of wholesale accounts that drop ship ordered products. They have a lot of order history in their accounts, and ideally need to be able to search by order number and shipping details (shipping first name, shipping last name, shipping address, etc).

Disclaimer: This shop has been fully converted to HPOS and the legacy data deleted and compatibility mode turned off.

I feel like I'm close, but the following code generates no results no matter what is queried in the form, messaging: "No order has been made yet."

function add_search_to_orders() {
    $allowed_roles = array('administrator', 'shop_manager');
    $user = wp_get_current_user();
    $user_roles = (array) $user->roles;

    if (array_intersect($allowed_roles, $user_roles)) {
        $show_clear_link = isset($_GET['search_orders']) && !empty($_GET['search_orders']);
        echo '<form method="get" id="orders-search-form">
                <input type="text" name="search_orders" placeholder="Search..." value="' . (isset($_GET['search_orders']) ? esc_attr($_GET['search_orders']) : '') . '" />
                <button type="submit">Search</button>';
        if ($show_clear_link) {
            echo '<a href="' . esc_url(remove_query_arg('search_orders')) . '">Reset Search</a>';
        }
        echo '</form>';
    }
}

add_action('woocommerce_before_account_orders', 'add_search_to_orders');

add_filter('woocommerce_my_account_my_orders_query', 'search_orders_query', 10, 1);
function search_orders_query($args) {
    if (isset($_GET['search_orders'])) {
        $search_term = sanitize_text_field($_GET['search_orders']);
        
        $args['meta_query'][] = array(
            'key'     => 'order_number',
            'value'   => $search_term,
            'compare' => 'LIKE'
        );

        $args['meta_query'][] = array(
            'relation' => 'OR',
            array(
                'key'     => 'shipping_first_name',
                'value'   => $search_term,
                'compare' => 'LIKE'
            ),
            array(
                'key'     => 'shipping_last_name',
                'value'   => $search_term,
                'compare' => 'LIKE'
            )
        );

        $args['meta_query']['relation'] = 'OR';
    }
    return $args;
}

I've added in some user role checks and also a reset search link for good measure.

How can I get this to show actual results?

Answer 1

Here is what I've finally ended up getting to work. Again, the shop is setup with HPOS only with no compatibility mode or syncing.

    add_action( 'woocommerce_before_account_orders', 'add_order_search_form' );
    function add_order_search_form() {
        $allowed_roles = array('administrator', 'shop_manager');
        $user = wp_get_current_user();
        $user_roles = (array) $user->roles;
    
        if (array_intersect($allowed_roles, $user_roles)) {
            $show_clear_link = isset($_GET['order_search']) && !empty($_GET['order_search']);
            echo '
                    
                    Search';
            if ($show_clear_link) {
                echo 'Reset Search';
            }
            echo '';
        }
    }
    
    function custom_order_search( $search_term, $customer_id ) {
        global $wpdb;
    
        $search_term = '%' . $wpdb->esc_like( $search_term ) . '%';
    
        $query = $wpdb->prepare(
            "SELECT DISTINCT o.id 
             FROM {$wpdb->prefix}wc_orders o
             LEFT JOIN {$wpdb->prefix}wc_order_addresses a 
                 ON o.id = a.order_id AND a.address_type = 'shipping'
             LEFT JOIN {$wpdb->prefix}wc_orders_meta m 
                 ON o.id = m.order_id AND m.meta_key = 'po_number'
             WHERE o.customer_id = %d
             AND (
                 o.id LIKE %s 
                 OR a.first_name LIKE %s 
                 OR a.last_name LIKE %s
                 OR a.company LIKE %s 
                 OR a.address_1 LIKE %s 
                 OR a.address_2 LIKE %s 
                 OR a.city LIKE %s 
                 OR a.state LIKE %s 
                 OR a.postcode LIKE %s
                 OR m.meta_value LIKE %s
             )",
            $customer_id,
            $search_term,
            $search_term,
            $search_term,
            $search_term,
            $search_term,
            $search_term,
            $search_term,
            $search_term,
            $search_term,
            $search_term
        );
    
        return $wpdb->get_col( $query );
    }
    
    add_filter( 'woocommerce_my_account_my_orders_query', 'filter_orders_by_search', 10, 1 );
    function filter_orders_by_search( $args ) {
        if ( isset( $_GET['order_search'] ) && ! empty( $_GET['order_search'] ) ) {
            $search_term = sanitize_text_field( $_GET['order_search'] );
            $customer_id = get_current_user_id();
    
            // Get matching order IDs from the custom search
            $order_ids = custom_order_search( $search_term, $customer_id );
    
            if ( ! empty( $order_ids ) ) {
                // Filter orders by the matching IDs
                $args['id'] = $order_ids;
            } else {
                // If no matches, return no orders
                $args['id'] = array( 0 );
            }
        }
    
        return $args;
    }