How to query serialized array by comparing greater than (>=)?

serialised array in a meta_value for query

It’s not a good idea to save serialised array in a meta_value if you plan to query with any value within that serialized array later. So, the best option is to save meta data as key / value pair.

Query Serialized array

Although it’s not a good approach, it’s still possible to query a serialized array within meta_value using regular expression.

Warning: Performance wise this is not a good idea at all. So if you have any other way to achieve the same thing, better do that. I’m giving the answer only to show that it’s possible. So use it only as a last resort.

Example CODE:

// this array is serialized and saved in meta_value for meta_key 'themeOps'
$serializedArray = array(
    'option1' => 7,
    'option2' => 'bar',
    'option3' => 'apple',
    'option4' => 'orange'
);

// this is the WP_Query argument
$args = array(
    'meta_query' => array(
        array(
            'key'     => 'themeOps',
            // this compares if 'option1' >= 6 within the serialized array in meta_value
            'value'   => wpse311157_serialize_num_greater_equals_regex( 'option1', 6 ),
            'compare' => 'REGEXP',
        )
    )
);

As you can see, I’ve used 'compare' => 'REGEXP' and used the function wpse311157_serialize_num_greater_equals_regex( 'option1', 6 ) to generate the proper regular expression (first param is the name of the array key and the second param is the number to compare with >=).

Now, let’s implement the wpse311157_serialize_num_greater_equals_regex function. Since meta_value is a serialized array, it’ll look something like: a:1:{s:3:"key";i:7;}. To match that, our CODE will look like:

function wpse311157_serialize_num_greater_equals_regex( $key, $num ) { 
    return 'a\:[1-9][0-9]*\:\{.*' . preg_quote( serialize( $key ) ) . 'i\:(' . wpse311157_num_greater_equals_regex( $num ) . ');.*\}';
}

Now we need to implement the wpse311157_num_greater_equals_regex( $num ) function by converting >= comparison into regular expression. This is not very efficient, but this is the only option we have.

RegEx algorithm to compare >=

The algorithm is simple:

(A) For any n digit number, any number with (n+1) digits is greater than that number.

(B) Additionally, we need maximum n number of rules to check other n digit numbers that are greater than or equal to this number.

For example, say we want to compare: num >= 12

So, RegEx: [1-9][0-9][0-9]+ will always satisfy it, as it’ll match 3 or more digit numbers.

Now, to match 2 digit numbers that are >= 12, we need 2 rules:

1. 1[2-9] => this will match numbers: 12 to 19
2. [2-9][0-9] => this will match numbers: 20 to 99

So the final RegEx for num >= 12 is: 1[2-9]|[2-9][0-9]|[1-9][0-9][0-9]+

With this algorithm, let’s create our wpse311157_num_greater_equals_regex( $num ) function:

function wpse311157_num_greater_equals_regex( $num ) {
    $digits = wpse311157_num_digits( $num );
    $num_i = $num;
    $regex = '';        
    for( $i = 1; $i <= $digits; $i++ ) { 
        $digit = $num_i % 10; 
        $num_i = (int) ( $num_i / 10 );
        $regex_i = ''; 
        $need_rule = true;
        if( 1 === $i ) { 
            if( 9 === $digit ) { 
                $regex_i = '9';
            }   
            else {
                $regex_i = '[' . $digit . '-9]';
            }   
        }   
        else {
            // no rule for 9
            if( 9 === $digit ) { 
                $need_rule = false;
            }
            else if( 8 === $digit ) {
                $regex_i = '9';
            }
            else {
                $regex_i = '[' . ( $digit + 1 ) . '-9]';
            }
        }

        if( $need_rule ) {
            if( $i < $digits ) {
                $regex_i = $num_i . $regex_i;
            }

            for( $j = 1; $j < $i; $j++ ) {
                $regex_i = $regex_i . '[0-9]';
            }

            if( empty( $regex ) ) {
                $regex = $regex_i;
            }
            else {
                $regex = $regex . '|' . $regex_i;
            }
        }
    }

    $regex = $regex . '|[1-9]';
    for( $i = 1; $i < $digits; $i++ ) {
        $regex = $regex . '[0-9]';
    }
    $regex = $regex . '[0-9]+';

    return $regex;
}

function wpse311157_num_digits( $num ) { 
    // not considering 0 or negative numbers
    if( $num < 1 ) return -1; 

    return floor( log10( $num ) + 1 );
}

That’s all, now you’ll be able to compare a value with >= within a serialized array.