meta_query with meta values as serialize arrays

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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: http://www.primothemes.com/forums/viewtopic.php?t=15658

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. */
            }
    }
?>

Answer 4

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'
        )
    )
    );

Answer 5

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' => '>'
        )
    )
);