Query meta field using between

by

Your problem is this:

CAST(wp_postmeta.meta_value AS SIGNED)

Run a simple SQL query (command line or PhpMyAdmin, whatever you like) like SELECT CAST('1.2' as SIGNED) and you will see that the value returned is 1. What causes that cast is 'type' => 'NUMERIC',.

You can pass DECIMAL to meta_query instead of NUMERIC but I don’t see a way to pass parameters to DECIMAL that would actually make it useful. SELECT CAST('1.2' as DECIMAL) is also 1 unless you also pass arguments to DECIMAL like this SELECT CAST('1.2' as DECIMAL(20,10)). The first number is ‘precision’– the number of decimal places used in the math– and the second is the number of decimal places returned. The second appears to me that the second value needs to be at least 2 less than the first or you get odd rounding issues. Obviously the second needs to be at or greater than the maximum decimal places in your number.

Your best approach would be to use 'type' => 'DECIMAL', and apply a filter:

function add_decimal_params($sqlarr) {
  remove_filter('get_meta_sql','add_decimal_params');
  $sqlarr['where'] = str_replace('DECIMAL','DECIMAL(20,16)',$sqlarr['where']);
  return $sqlarr;
}
add_filter('get_meta_sql','add_decimal_params');

Add the filter immediately before your query and it will remove itself.

You may be able to get this to work by left-padding your numbers to the same length and not using a type argument at all, but I didn’t test that.

Leave a Comment