0

How do I do a 'like' comparison on a numeric value with meta_query?

I have some code that does an autocomplete search for product sku's in WooCommmerce. It works fine for non numeric skus, but I can't get it working with numeric skus. I'm using compare => 'like' because my autocomplete script is setup to start searching after 2 characters have been entered (so that skus 10 characters long can be searched instead of entered exactly). Here's what I'm working with now:

$products1 = array(
    'post_type' => array ('product', 'product_variation'),
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'meta_query' => array(
        array(
        'key' => '_sku',
        'value' => $_REQUEST['term'],
        'compare' => 'LIKE'
        )
    ),
    'fields' => 'ids'
);
0

There is no reason that the meta query should not work. The database column is varchar so your number is going to be treated as a string. I tested as best I can without having your database and the testing seems to confirm that.

I think you are going to get a lot of spurious matches. The default LIKE search will put a % wildcard at each end of the string so that 123 will match anywhere in the string, for example, here-- 12345678-- but also here-- 45678123. I suspect that you only want a one sided LIKE so that 123 matches but at the front of 12345123 but not the end. You need a filter for that.

function one_sided_like($sql) {
  remove_filter('get_meta_sql','wildcard_meta_like');
  $pat = '%([^%]*)%';
  preg_match('|'.$pat.'|',$sql['where'],$matches);
  if (!empty($matches[1])) {
    $sql['where'] = str_replace($matches[0],$matches[1].'%',$sql['where']);
  }
  return $sql;
}
add_filter('get_meta_sql','one_sided_like');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.