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.

Solution

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.