Wordpress meta_query an array of values when metadata to compare is serialized array?

Question

I am trying to run a meta_query with an array of values and have it search if all are present in the meta value which is stored in a serialized array. Is this possible?

My arguments for the query is as follows (note that this is nested in a class):

$args = array(
    'post_type' => $this->posttype,
    'posts_per_page' => '9',
    'paged' => $paged,
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
    'meta_key' => 'lumens',
    'meta_query' => array(
        array(
            'key' => 'mount',
            'value' => array('pendant' , 'wall'),
            'compare' => 'IN'
        )

    )
);

An example of the meta data stored, is in a serialized array similar to below:

a:4:{i:0;s:7:"pendant";i:1;s:15:"surface-ceiling";i:2;s:4:"wall";i:3;s:14:"aircraft-cable";}

My query will not return the appropriate results no matter what I try. I am realizing now that I probably should have stored each value in a different meta-key rather then in an array, however, there is far to many entries already to change the metadata now.

UPDATE:

This was my workaround, similar to @Leander approach; I did not want to alter the serialized inputs due to the amount of entries already on the database and there was one thing I forgot to mention, I was utilizing the CMB2 Developer Toolkit which stores checkbox fields as serialized data natively.

// This is pulled from a user input
$meta_queries = array('pendent' , 'wall');

// Metaqueries are passed through loop to create single entries with the like comparison operator
foreach($meta_queries as $key => $value){

    $meta_query = array(
        'key' => '_tf_' . $key,

        // Serialize the comparison value to be more exact
        'value' => serialize(strval($value)),
        'compare' => 'LIKE',
    );

}

// Generate $args array
$args = array(

    'post_type' => $this->posttype,
    'posts_per_page' => '9',
    'paged' => $paged,
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
    'meta_key' => 'lumens',
    'meta_query' => $meta_queries

);

I did not notice much of a performance issue while populating the data. I imagine this approach would have to be reworked if there was an overwhelming amount of data to process.

Answer 1

Since the metadata is serialized in the database column, and is technically represented as a string, you can do a REGEXP compare.

Referenced by your example.

meta_value (database)

a:4:{i:0;s:7:"pendant";i:1;s:15:"surface-ceiling";i:2;s:4:"wall";i:3;s:14:"aircraft-cable";}

Query

$args = array(
    'post_type' => $this->posttype,
    'posts_per_page' => '9',
    'paged' => $paged,
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
    'meta_key' => 'lumens',
    'meta_query' => array(
        array(
            'key' => 'mount',
            'value' => '"(pendant|wall)"',
            'compare' => 'REGEXP'
        )

    )
);

Or if you have an array of values you can do the following.

array(
     'key' => 'mount',
     'value' => '"('. implode('|', array('pendant' , 'wall')) .')"',
     'compare' => 'REGEXP'
)

Note

If your "value" values contain special characters that need to be escaped for valid regex use the preg_quote function. Otherwise this solution should work 100%.

Answer 2

Far too many entries to change the meta data now

Then run a script (PHP script or MySQL directly) and create proper meta_key => meta_value pairs, then delete your serialized column. Serialized data should be stored in the database only if they are to retrieved as such. If you are lazy and still want to have only one column, store the data as JSON and then query them as JSON (modern versions of MySQL allow to query json data from a column of type JSON). Good luck with that.

But if you insist and keep the data as serialized, I assure you, you or whoever maintains your project will keep banging his/her head for every related small feature you need to implement.

Answer 3

Just ran across the same problem...!

I have some tags stored in an array / custom field and am using a search form to query my posts where the user should be allowed to search for multiple tags so essentially I need to compare an array against another array.

I am realizing now that I probably should have stored each value in a different meta-key rather then in an array, however, there is far to many entries already to change the metadata now.

I'd imagine that would produce quite the overhead, wouldn't recommend that...

Approach / Workaround

I work with the user input as string, create an array out of the string to check for its size and depending on the size I am creating single LIKE-comparisons which work with my array'ed data just fine.

$tags_string = get_query_var( 'p_tags' ); // form submitted data
$tags_array = explode( ',', $tags_string ); // create array

if ( count( $tags_array ) > 1 ) { // check if more then one tag
$meta_query['p_tags']['relation'] = 'AND';

foreach($tags_array as $tag) { // create a LIKE-comparison for every single tag
    $meta_query['p_tags'][] = array( 'key' => 'YOUR_KEY', 'value' => $tag, 'compare' => 'LIKE' );
}
} else { // if only one tag then proceed with simple query
    $meta_query['p_tags'] = array( 'key' => 'YOUR_KEY', 'value' => $tags_string, 'compare' => 'LIKE' );
}

Args output (demo)

[meta_query] => Array
        (
            [p_tags] => Array
                (
                    [relation] => AND
                    [0] => Array
                        (
                            [key] => basic_tags
                            [value] => adobe
                            [compare] => LIKE
                        )

                    [1] => Array
                        (
                            [key] => basic_tags
                            [value] => stone
                            [compare] => LIKE
                        )

                )

        )

Notice: Depending on the size of your array(s), the number of posts to be queried etc. this solution might not be the most performant available.

Another approach might be the FIND_IN_SET extension for WordPress queries, see this gist.

Appreciate any input regarding performance or improving the code quality.