MySQL LIKE %filter% equivalent when query with wc_get

First, sorry for my ignorance in this field and I will try to express myself clear.

I am trying to use wc_get_orders to get orders from Woocommerce with the following args

$args = array(
  'limit' => $rows_per_page,
  'paged' => $page,
  'paginate' => true,
  'customer' => $filter
);
$results = wc_get_orders($args);
$orders = $results->orders;

code taken from https://github.com/woocommerce/woocommerce/wiki/wc_get_orders-and-WC_Order_Query#customer and modified by me.

$filter is a partial string of customer name or email. With this setup, I can only filter and receive result when $filter is full: [email protected]. I am trying to partially match %@woo% just like in MySQL with no luck.

Currently, I solve this with a raw query as follow (I understand that it is not a best practice and can break when woo commerce update their database):

global $wpdb;
$rows_per_page = intval((isset($_POST['rowsPerPage'])) ? sanitize_text_field($_POST['rowsPerPage']) : '5');
$page = intval((isset($_POST['page'])) ? sanitize_text_field($_POST['page']) : '1');
$filter = ((isset($_POST['filter'])) ? sanitize_text_field($_POST['filter']) : '') === 'undefined' ? '' : $_POST["filter"];

$skip = ($page - 1) * $rows_per_page;

$sqlCount = "
      SELECT 
      SQL_CALC_FOUND_ROWS {$wpdb->prefix}posts.ID 
   FROM 
      {$wpdb->prefix}posts 
   WHERE 
      1 = 1 
      AND wp_posts.post_type = 'shop_order' 
      AND (
      (
         {$wpdb->prefix}posts.post_status = 'wc-pending'
         OR {$wpdb->prefix}posts.post_status = 'wc-processing'
         OR {$wpdb->prefix}posts.post_status = 'wc-on-hold'
         OR {$wpdb->prefix}posts.post_status = 'wc-completed'
         OR {$wpdb->prefix}posts.post_status = 'wc-cancelled' 
         OR {$wpdb->prefix}posts.post_status = 'wc-refunded'
         OR {$wpdb->prefix}posts.post_status = 'wc-failed'
      )
      ) 
      AND {$wpdb->prefix}posts.ID IN (
         SELECT post_id AS order_id
         FROM {$wpdb->prefix}postmeta 
         WHERE meta_key 
         IN ('_billing_first_name', '_billing_email', '_billing_phone' )
         AND meta_value LIKE '%" . $filter . "%'
      )
   ORDER BY
      {$wpdb->prefix}posts.post_date DESC
";

$sql = "
      SELECT 
      SQL_CALC_FOUND_ROWS {$wpdb->prefix}posts.ID 
   FROM 
      {$wpdb->prefix}posts 
   WHERE 
      1 = 1 
      AND wp_posts.post_type = 'shop_order' 
      AND (
        (
          {$wpdb->prefix}posts.post_status = 'wc-pending' 
          OR {$wpdb->prefix}posts.post_status = 'wc-processing' 
          OR {$wpdb->prefix}posts.post_status = 'wc-on-hold' 
          OR {$wpdb->prefix}posts.post_status = 'wc-completed' 
          OR {$wpdb->prefix}posts.post_status = 'wc-cancelled' 
          OR {$wpdb->prefix}posts.post_status = 'wc-refunded' 
          OR {$wpdb->prefix}posts.post_status = 'wc-failed'
        )
      )
      AND {$wpdb->prefix}posts.ID IN (
         SELECT post_id AS order_id
         FROM {$wpdb->prefix}postmeta 
         WHERE meta_key 
         IN ('_billing_first_name', '_billing_email', '_billing_phone' )
         AND meta_value LIKE '%" . $filter . "%'
      )
   ORDER BY 
      {$wpdb->prefix}posts.post_date DESC 
   LIMIT 
      " . $skip . ", " . $rows_per_page . "       
";
$orders = $wpdb->get_results($sql);
$rowcount = count($wpdb->get_results($sqlCount));

I am not entirely happy with my raw sql setup because it may not be the best way to do such thing and I need to run 2 sql queries, one to get the paginated result, one to count the number of results returned. Is there a better way to do this?

Thank you very much.

CodePudding user response：

/**
 * Handle a custom 'customvar' query var to get orders with the 'customvar' meta.
 * @param array $query - Args for WP_Query.
 * @param array $query_vars - Query vars from WC_Order_Query.
 * @return array modified $query
 */
function handle_custom_query_var( $query, $query_vars ) {
    if ( ! empty( $query_vars['customvar'] ) ) {
        $query['meta_query'][] = array(
            'key' => 'customvar',
            'value' => esc_attr( $query_vars['customvar'] ),
            'compare' => 'LIKE'
        );
    }

    return $query;
}
add_filter( 'woocommerce_order_data_store_cpt_get_orders_query', 'handle_custom_query_var', 10, 2 );

Using LIKE inside wc_get_orders() with custom values can be done like the above code snippet. You can find more details here

CodePudding user response：

I found out how to do this. First, this is my $args:

$args = array(
  'limit' => $rows_per_page,
  'paged' => $page,
  'status' => $status,
  'paginate' => true,
  'meta_query' => array(
    'relation' => 'OR',
    array(
      'key'     => '_billing_first_name',
      'value'   => esc_attr($filter),
      'compare' => 'LIKE'
    ),
     array(
       'key'     => '_billing_email',
       'value'   => esc_attr($filter),
       'compare' => 'LIKE'
     ),
     array(
       'key'     => '_billing_phone',
       'value'   => esc_attr($filter),
       'compare' => 'LIKE'
     )
   )
);

To this point wc_get_orders won't work.

$results = wc_get_orders($args);

We need to tell Wordpress/WooCommerce to take the meta_query into consideration:

add_filter('woocommerce_get_wp_query_args', function ($wp_query_args, $query_vars) {
   if (isset($query_vars['meta_query'])) {
      $meta_query = isset($wp_query_args['meta_query']) ? $wp_query_args['meta_query'] : [];
      $wp_query_args['meta_query'] = array_merge($meta_query, $query_vars['meta_query']);
   }
   return $wp_query_args;
}, 10, 2);

This wonderful code is from this answer: https://wordpress.stackexchange.com/a/337865