Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a serialized array as a meta value and I'm trying to run WP Query where it finds a specific value in the serialized array.

Here's an example of the serialized array field:

a:6:{i:0;s:3:"173";i:1;s:3:"172";i:2;s:3:"171";i:3...

Here's my query, but it doesn't work: The ID's are numberic I'm doing a search for as well.

$practiceArgs = array(
    'post_type' => 'attorney',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'meta_query' => array(
                        array(
                              'key' => 'practice_area',
                              'value' => $post->ID,
                              'compare' => 'LIKE',
                              'type' => 'NUMERIC'
                              )
                        )
);

I've tried using 'value' => '%'.$post-ID.'%' in my queries, but it doesn't seem to work as well.

Anyone ever had any experience working on this?

share|improve this question
add comment

2 Answers

I ran into this issue once, but it's definitely not a nice place to be. The way you're approaching this you'll never have a consistent result -- your search will turn a lot of false positives (i.e. searching for post ID 43 will return 143, 243, 1431, 1432, etc.

The way I see it, you have two options:

  • Rethink the way you're saving your meta data; use arrays for data
    that's only needed when rendering the post, but not for data that you need to query to filter posts. For the latter, use individual meta keys in your db -- it's much faster, the queries are easier to code, and you can rest assured that you'll have full control over what the queries return. Don't be concerned with using too many rows in the postmeta table: that's why they're there for.
  • If for some reason you must have multiple fields packed in one db
    row, don't rely on default serialization: go for CSV, preferably with some sort of prefix, something that won't return false positives. Something like "id:43,id:54,foo:bar,id:57,", then search for 'id:{post_ID},' (note that all values should end with commas in order to safely use double wildcards on a LIKE search. It's sort of a hack, but it's more reliable than what you're going for right now.

For what it's worth, regarding the original question, WP already adds both wildcards when doing LIKE comparisons using query_posts, so you don't have to do anything.

share|improve this answer
add comment

You can't use LIKE with numeric (at least I am pretty sure you can't). numeric will try to convert the whole value to a number. That will fail, as it isn't a number. That is the first mistake.

Secondly, to get a literal %, you need to double it to %%. Note: I can't remember about meta_query strictly speaking. I am extrapolating from what it takes to get a % to pass through $wpdb->prepare I think it is the same though. Edit: This appears to only be true with $wpdb->prepare. The code has been edited.

I have not tested this, as I'd need to setup some things in my database and don't have time right now, but I think it will work.

$practiceArgs = array(
    'post_type' => 'attorney',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'meta_query' => array(
      array(
        'key' => 'practice_area',
        'value' => $post->ID,
        'compare' => 'LIKE'
      )
    )
);

I hope that is the push you need. If it doesn't work I will revisit.

Edit: I inserted your (broken) serialized array into my database and ran the code above. If I get everything set up correctly-- the meta_fields, the post types, etc.-- the code above works. If it isn't working for you you've got something else going on-- maybe you think there should be a match where there isn't.

I am assuming that you are going to have an array like that for every one of your 'attorney' posts. If you do, that should work. I might lean toward multiple practice_area rows though, instead of using a serialized array. = is a more efficient query than LIKE, especially when you have wildcards, and your results should be more accurate. Indexing (MySQL database) should work better as well.

share|improve this answer
    
Unfortunately, it still returns NIL. Any other ideas? –  Troy Feb 8 '13 at 18:59
    
Your comment regarding '%' is only true when using $wpdb->prepare. When using query_posts, using '%' is unnecessary: WP puts them in for you, whether you want them or not. –  tbuteler Feb 9 '13 at 14:25
    
@tbuteler WP will also automatically escape % to %% in that case. –  Robbert Dec 10 '13 at 7:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.