Posted on

WooCommerce: Searching for orders

Searching for shop orders is tricky with WooCommerce. WooCommerce blocks searching the orders. WooCommerce also keeps the order data in many different tables.

Because of this complexity, the shop_order post type is not available for indexing. You can enable it by adding this to your site:

add_filter(
  'option_relevanssi_index_post_types',
  function( $post_types ) {
    $post_types[] = 'shop_order';
    return $post_types;
  }
);

This is not enough, though. The order use custom statuses. You also need to allow these statuses with this code snippet:

add_filter( 'relevanssi_valid_status', 'rlv_add_status' );
add_filter( 'relevanssi_valid_admin_status', 'rlv_add_status' );

function rlv_add_status( $status_array ) {
  $status_array[] = 'wc-completed';
  $status_array[] = 'wc-pending';
  return $status_array;
}

Now the orders are available in the Relevanssi admin search. They are still excluded from the front end search. Relevanssi indexes very little information about the order by default. You only get the order date.

WooCommerce stores the items in the order in wp_woocommerce_order_items. Some stats about the order are in wp_wc_order_stats. Customer information is in wp_wc_customer_lookup. Some information is in wp_postmeta, too. We need a relevanssi_content_to_index function that compiles all this information.

This will get you started with indexing the content for the orders. You can use this as a framework to add more information to the orders:

add_filter( 'relevanssi_content_to_index', 'rlv_index_order_content', 10, 2 );
function rlv_index_order_content( $content, $post ) {
  if ( 'shop_order' === $post->post_type ) {
    global $wpdb;

    // Order number.
    $content .= ' ' . $post->ID;
    
    // Product names.
    $item_names = $wpdb->get_col(
      $wpdb->prepare( "SELECT order_item_name FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id = %d", $post->ID )
    );
    $content .= ' ' . implode( ' ', $item_names );

    // Total sum of the order.
    $order_stats = $wpdb->get_results(
      $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wc_order_stats WHERE order_id = %d", $post->ID )
    );
    $content .= ' ' . $order_stats->total_sales;

    // Customer first and last name.
    $customer = $wpdb->get_results(
      $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wc_customer_lookup WHERE customer_id = %d", $order_stats->customer_id )
    );
    $content .= ' ' . $customer->first_name . ' ' . $customer->last_name;

    // Order meta, for example billing and shipping addresses.
    $meta = get_post_meta( $post->ID );
    $meta = array_combine( array_keys( $meta ), array_column( $meta, '0' ) );

    $content .= ' ' . $meta['_billing_address_index'];
    $content .= ' ' . $meta['_shipping_address_index'];

    // Customer Provided Note.
    $content .= $post->post_excerpt;
  }
  return $content;
}

Leave a Reply

Are you a Relevanssi Premium customer looking for support? Please use the Premium support form.

Your email address will not be published. Required fields are marked *