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'm working on a project in which I'm creating a custom post type and custom data entered via meta boxes associated with my custom post type. For whatever reason I decided to code the meta boxes in such a way that the inputs in each metabox are part of an array. For instance, I'm storing longitude and latitude:

<p> 
    <label for="latitude">Latitude:</label><br /> 
    <input type="text" id="latitude" name="coordinates[latitude]" class="full-width" value="" /> 
</p> 
<p>     
    <label for="longitude">Longitude:</label><br /> 
    <input type="text" id="longitude" name="coordinates[longitude]" class="full-width" value="" /> 
</p>

For whatever reason, I liked the idea of having a singular postmeta entry for each metabox. On the save_post hook, I save the data like so:

update_post_meta($post_id, '_coordinates', $_POST['coordinates']);

I did this because I have three metaboxes and I like just having 3 postmeta values for each post; however, I've now realized a potential issue with this. I may want to use WP_Query to only pull out certain posts based these meta values. For instance, I may want to get all posts that have latitude values above 50. If I had this data in the database individually, perhaps using the key latitude, I would do something like:

$args = array(
    'post_type' => 'my-post-type',
    'meta_query' => array(
        array(
            'key' => 'latitude',
            'value' => '50',
            'compare' => '>'
        )
    )
 );
$query = new WP_Query( $args );

Since I have the latitude as part of the _coordinates postmeta, this would not work.

So, my question is, is there a way to utilize meta_query to query a serialized array like I have in this scenario?

share|improve this question

6 Answers 6

up vote 14 down vote accepted
+50

No, it is not possible.

I strongly recommend you unserialise your data and modify your save routine. Something similar to this should convert your data to the new format:

$args = array(
    'post_type' => 'my-post-type',
    'meta_key' => '_coordinates',
    'posts_per_page' => -1
 );
$query = new WP_Query( $args );
if($query->have_posts()){
    while($query->have_posts()){
        $query->the_post();
        $c = get_post_meta($post->id,'_coordinates',true);
        add_post_meta($post->ID,'_longitude',$c['longitude']);
        add_post_meta($post->ID,'_latitude',$c['latitude']);
        delete_post_meta($post->ID,'_coordinates',$c);
    }
}

Then you'll be able to query as you want with individual keys

share|improve this answer
1  
Thanks for this answer! Just revisited this today and marked your answer as correct! –  tollmanz Jan 17 '13 at 22:15

You really are going to lose the ability to query your data in any efficient manner when serializing entries into the WP database.

The overall performance saving and gain you think you are achieving by serialization is not going to be noticeable to any major extent. You might obtain a slightly smaller database size but the cost of SQL transactions is going to be heavy if you ever query those fields and try to compare them in any useful, meaningful manner.

Instead, save serialization for data that you do not intend to query in that nature, but instead would only access in a passive fashion by the direct WP API call get_post_meta() - from that function you can unpack a serialized entry to access its array properties too.

In fact assigned the value of true as in;

$meta = get_post_meta( $post->ID, 'key', true );

Will return the data as an array, accessible for you to iterate over as per normal.

You can focus on other database/site optimizations such as caching, CSS and JS minification and using such services as a CDN if you require. To name but a few.... WordPress Codex is a good starting point to uncover more on that topic: HERE

share|improve this answer

I've just dealed with serialized fields and could query them. Not using the meta_query but using a SQL query.

global $wpdb; 

$search = serialize('latitude').serialize(50);

$query = $wpdb->prepare("SELECT `post_id`
FROM `wp_postmeta`
WHERE `post_id` IN (SELECT `ID` FROM `wp_posts` WHERE `post_type` = 'my-post-type')
AND `meta_key` = '_coordinates'
AND `meta_value` LIKE '%s'",'%'.$search.'%');

$ids = $wpdb->get_col($query);

$args = array(
    'post__in' => $ids
    'post_type' => 'team' //add the type because the default will be 'post'
);

$posts = get_posts($args);

The query first searches for post with the matching post_type so the amount of wp_postmeta records will be less to filter. Then i've added a where statement to reduce the rows further by filtering on meta_key

The IDs end up nicely in an array as needed for get_posts.

PS. MySQL v5.6 or higher is needed for good subquery performance

share|improve this answer

This example really helped me. It's specifically for S2Members plugin (which serializes user metadata). But it allows you to query a portion of a serialized array within the meta_key.

It works by using the MySQL REGEXP function.

Here is the source

Here is the code that queries all users living in the US. I easily modified it to query one of my custom registration fields and had it working in no time.

  <?php
global $wpdb;
$users = $wpdb->get_results ("SELECT `user_id` as `ID` FROM `" . $wpdb->usermeta . 
          "` WHERE `meta_key` = '" . $wpdb->prefix . "s2member_custom_fields' AND 
           `meta_value` REGEXP '.*\"country_code\";s:[0-9]+:\"US\".*'");
if (is_array ($users) && count ($users) > 0)
    {
        foreach ($users as $user)
            {
                $user = /* Get full User object now. */ new WP_User ($user->ID);
                print_r($user); /* Get a full list of properties when/if debugging. */
            }
    }
?>
share|improve this answer

I have the same question. Maybe you need the 'type' parameter? Check out this related question: Custom Field Query - Meta Value is Array

Perhaps try:

    $args = array(
    'post_type' => 'my-post-type',
    'meta_query' => array(
        array(
            'key' => 'latitude',
            'value' => '50',
            'compare' => '>',
            'type' => 'numeric'
        )
    )
    );
share|improve this answer
    
Thanks for the suggestion, but this isn't quite what I'm after. The problem is that the value I'm trying to match is a part of an array that is serialized within the database. –  tollmanz May 13 '11 at 3:10
    
Yea, you're right. I tried this this morning and it didn't work for me either. I have the same issue. Storing a value of a meta key as an array. I'm starting to think this can't be done and I might instead have to store them as separate meta fields with the same name...and just manage the deleting/updating of them properly. –  user4356 May 13 '11 at 15:05
    
@user4356...that's exactly what I'm going to do. I was hoping to cut down on the number of rows that I would insert for each post, but I guess that's not possible. –  tollmanz May 14 '11 at 19:15

I ran into something similar while using the Magic Fields plugin. This might do the trick

$values_serialized = serialize(array('50'));
$args = array(
    'post_type' => 'my-post-type',
    'meta_query' => array(
        array(
            'key' => 'latitude',
            'value' => $values_serialized,
            'compare' => '>'
        )
    )
);
share|improve this answer
1  
Thanks for the suggestion! I think this is as close as one can get, but it won't actually work because comparing a serialized array to another serialized array doesn't made sense unless I was looking for an exact match. –  tollmanz May 13 '11 at 3:08
5  
Then this shouldn't be marked as the correct answer and it is irresponsible of you to do so. The correct answer thus would be 'No, it's not possible' –  Tom J Nowell Aug 20 '12 at 15:14
1  
Agree, also WP handles serialization for you, serialize() is not required in this instance... –  userabuser Aug 20 '12 at 16:11
1  
Actually @seth-stevenson answer is great when doing exactly what he said, using "Magic Fields" plugin. As that plugin serializes certain data type by default, this is the best way to do an EXACT match. –  zmonteca Jan 9 '13 at 5:02
    
@TomJNowell Done! Just took me 5 months ;) –  tollmanz Jan 17 '13 at 22:15

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.