How do I search an array stored in a custom-field using WP_Query?

Question

I have a custom post type called "on-air" that included a custom-field called "daysonair". The form for this has checkboxes for each day of the week.

When you add/update a post, the checked items are stored as an array in the custom-field.

I can't figure out how to add that custom-field to my WP_Query() search:

array( 'key' => 'daysonair',
       'value' => 'Thursday',
       'compare' => 'IN' )

I've tried numerous types of comparisons with no luck.

The data in the database is:

a:5:{s:6:"Monday";s:6:"Monday";s:7:"Tuesday";s:7:"Tuesday";s:9:"Wednesday";s:9:"Wednesday";s:8:"Thursday";s:8:"Thursday";s:6:"Friday";s:6:"Friday";}

Where all the weekdays have been checked. I'm just looking to see if Thursday is in the array.

Answer 1

Searching inside a serialized array is difficult and inefficient-- ie. slow. The following pure SQL will do it:

SELECT *
FROM `wp_postmeta`
WHERE `meta_key` LIKE 'daysonair'
AND `meta_value` LIKE '%thursday%'
LIMIT 0 , 100

Something like this should get WP_Query to do something similar:

$args = array(
  'post_type' => 'post',
  'meta_query' => array(
    array(
      'key' => 'daysonair',
      'value' => 'thursday',
      'compare' => 'LIKE',
    )
  )
);
$q = new WP_Query($args);
var_dump($q);

But LIKE queries with wildcards-- %-- are very slow. The query has to check every row in the table for a match.

Additionally, this will be prone to error as any occurance of "thursday" will match, for example, something in an attachment image alt text.

The problem is that a serialize string is a PHP construct. MySQL does not know what to do with it other than treat it like any other string.

The only really good fix, and I don't know if it possible in you case, is to change how the data is stored in the database. If you need to search the data it should not be stored as a serialized string. Each piece of data should be stored as an independent key/value pair.

Answer 2

A custom LIKE query will sort of work, but I personally wouldn't trust it for anything other than the simplest implementation (as s_ha_dum said in the comments).

A better solution is to create a custom taxonomy for "days of the week", and run a tax query based on that. This is exactly the kind of thing that the taxonomy system is designed for.

$args = array(
    'tax_query' => array(
        array(
            'taxonomy' => 'days_of_week',
            'field' => 'slug',
            'terms' => 'wednesday'
        )
    )
);
$query = new WP_Query( $args );