3

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.

5

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.

  • You will be back later complaining that "Sometimes it does XYZ. How do I fix that?" :) – s_ha_dum Sep 27 '13 at 14:43
  • Searching for the value ':Thursday;' will match the serialized value more closely, and keep it from returning most potential false positives. – J.D. Dec 7 '13 at 20:18
  • It would need to be :"Thursday";, but it is never going to be an especially efficient query. – s_ha_dum Dec 7 '13 at 20:50
1

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 );
  • +1 From my own answer "The only really good fix... is to change how the data is stored in the database." – s_ha_dum May 2 '14 at 15:23

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.