2

I have serialized post meta values. It looks like this:

enter image description here

In array:

$kisiArray = array(
   'option1' => 7,
   'option2' => 'bar',
   'option3' => 'apple',
   'option4' => 'orange'
);

And I want to make a custom query with meta_query args. This is my query args:

<?php 
$args = array(
    'order'          => 'DESC',
    'meta_query'     => array(
        array(
            'key'     => 'themeOps_option1',
            'value'   => 6,
            'compare' => '>=',
        )
    )
);
?>

But it doesn't display any result.

  • 1
    You can’t. If you intend to query a value then you should not store it in a serialised array. – Jacob Peattie Aug 12 '18 at 1:40
  • Actually it's possible, although not recommended. – Fayaz Aug 12 '18 at 15:52
2

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.

  • 1
    pity I can not upvote only the first paragraph ;) the rest, while it will work most of the time of most of the people is a trap as not only it will stop working for some values but it will also make the poor performances of meta searches even worse (I know that you know, I just feel like it needs to be said next to the answer) – Mark Kaplun Aug 12 '18 at 16:17
  • 1
    @MarkKaplun: I agree with you 100% :) Edited to make the warning more prominent. – Fayaz Aug 12 '18 at 16:41
  • 1
    Omg. It is amazing. But i followed your recommendation. So i created new single meta_key with single value (not serialized) Thank you for all. – bilimokur Aug 12 '18 at 16:44
  • @bilimokur yes, that's the right way :) – Fayaz Aug 12 '18 at 16:46

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.