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?

12 Answers 12

up vote 35 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

If you need to store multiple longitudes, and multiple latitudes, you can store multiple post meta with the same name. Simply use the third parameter of get_post_meta, and it will return them all as an array

Why Can't You Query Inside Serialised Data?

MySQL sees it as just a string, and can't break it apart into structured data. Breaking it apart into structured data is exactly what the code above does

You may be able to query for partial chunks of date, but this will be super unreliable, expensive, slow, and very fragile, with lots of edge cases. Serialised data isn't intended for SQL queries, and isn't formatted in a regular and constant way.

Aside from the costs of partial string searches, post meta queries are slow, and serialised data can change depending on things such as the length of contents, making searching incredibly expensive, if not impossible depending on the value you're searching for

A Note on Storing Records/Entities/Objects as Serialised Objects in Meta

You might want to store a transaction record in post meta, or some other kind of data structure in user meta, then run into the problem above.

The solution here is not to break it out into individual post meta, but to realise it should never have been meta to begin with, but a custom post type. For example, a log or record can be a custom post type, with the original post as a parent, or joined via a taxonomy term

  • 5
    For people passing by, don't stop reading: more useful (and recent) answers are found below – Erenor Paz Apr 13 '17 at 10:07
  • What if I have an array of IDs to save - and they do not each represent a different key I could save them under such as 'latitude' etc., it's just one key for all (such as when saving relations etc.). What to do then? @rabni's solution? – trainoasis Jun 16 '17 at 7:52
  • You can store a key more than once, key value pairs are not unique. As for relations, that's what taxonomies are for, if you're using meta to map multiple things onto something, put them in a taxonomy term instead – Tom J Nowell Jun 19 '17 at 17:31

I also run into this situation. Here what i did:

$args = array(
    'post_type' => 'my-post-type',
    'meta_query' => array(
        array(
            'key' => 'latitude',
            'value' => sprintf(':"%s";', $value),
            'compare' => 'LIKE'
        )
    )
);

Hope this help

  • 1
    I really liked this solution. Unfortunately, this is not applicable when the $value is also an ID. In that case, i suggest to create to functions to add a character to each array element before saving the data and another function to remove the character before using the data. This wat, the serialized i:2 index will not be confused with the i:D2 of the "real" data. The meta query parameter should then become 'value' => sprintf(':"D%s";', $value), and you will keep the correct functionality of this wonderful answer! – Erenor Paz Apr 13 '17 at 9:30
  • This solution is working for me – Vishal Jan 12 at 9:32
  • This also worked perfectly for me. Did have a mini panic when I saw the accepted solution though – Shane Jones Feb 26 at 10:48
  • @Erenor Paz, I just posted a solution which works well with both ID and Strings: wordpress.stackexchange.com/a/299325/25264 – Pablo S G Pacheco Mar 29 at 19:09
  • using LIKE is a great and fast way to bring your server down (not to mention false positives) you better have a very good caching. – Mark Kaplun Apr 2 at 5:23

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

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

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

I think there are 2 solutions that can try to solve the problem of results being stored as both String and Integers. However, it's important to say, as others pointed out, that it is not possible to guarantee the integrity of results stored as Integer, because as these values as stored as serialized arrays, the index and the values are stored exactly with the same pattern. Example:

array(37,87);

is stored as a serialized array, like this

a:2:{i:0;i:37;i:1;i:87;}

Note the i:0 as the first position of the array and i:37 as the first value. The pattern is the same. But let's go to the solutions


1) REGEXP Solution

This solution works for me regardless of the meta value being saved as string or number / id. However it uses REGEXP, which is not so fast as using LIKE

$args = array(
    'post_type' => 'my-post-type',
    'meta_query' => array(
        array(
            'key' => 'latitude',
            'value' => '\;i\:' . $value . '\;|\"' . $value . '\";',
            'compare' => 'REGEXP'
        )
    )
);

2) LIKE Solution

I'm not sure about the performance difference but this is a solution that uses LIKE and also works for both number and strings

 $args = array(
        'post_type' => 'my-post-type',
        'meta_query' => array(
            'relation' => 'OR',
            array(
                'key' => 'latitude',
                'value' => sprintf(':"%s";', $value),
                'compare' => 'LIKE'
            ),
            array(
                'key' => 'latitude',
                'value' => sprintf(';i:%d;', $value),
                'compare' => 'LIKE'
            )
        )
    );
  • REGEXP is nice in certain situations, but if you can use LIKE, I think it's the preferable method. An old link, but still quite useful, in my opinion: thingsilearn.wordpress.com/2008/02/28/… :-) – Erenor Paz Mar 30 at 8:44
  • @ErenorPaz You are right. LIKE is faster. But this is a solution that works for both strings and numbers – Pablo S G Pacheco Mar 30 at 20:37
  • Yes..so, the answer is (as always): depending on the situation, if you can use "LIKE"; it's preferable, otherwise REGEXP will do as well :-) – Erenor Paz Mar 31 at 13:10
  • @ErenorPaz, I edited my answer adding a new solution that uses LIKE but works for both numbers and strings. I'm not sure about the performance because it has to compare the results using OR – Pablo S G Pacheco Apr 1 at 19:41
  • Exactly !!! which i need to get result same like this.... Thanks Man!!! – kuldip Makadiya Sep 27 at 11:23

After reading a bunch of tips for running a WP_Query filtering by serialized arrays, here's how I finally did it: by creating an array of comma separated values using implode in conjunction with a $wpdb custom SQL query utilizing FIND_IN_SET to search the comma separated list for the requested value.

(this is similar to Tomas's answer, but its a bit less performance intensive for the SQL query)

1. In functions.php:

In your functions.php file (or wherever you're setting up the meta box) in the yourname_save_post() function use

update_post_meta($post->ID, 'checkboxArray', implode(",", $checkboxArray)); //adding the implode

to create the array containing comma separated values.

You'll also want to change your output variable in the yourname_post_meta() admin meta box construction function to

$checkboxArray = explode(",", get_post_custom($post->ID)["checkboxArray"][0]); //adding the explode

2. In the template PHP file:

Test: if you run a get_post_meta( $id ); you should see checkboxArray as an array containing your comma separated values instead of a serialized array.

Now, we build our custom SQL query using $wpdb.

global $wpdb;

$search = $post->ID;

$query = "SELECT * FROM wp_posts
          WHERE FIND_IN_SET( $search, (
              SELECT wp_postmeta.meta_value FROM wp_postmeta
              WHERE wp_postmeta.meta_key = 'blogLocations'
              AND wp_postmeta.post_id = wp_posts.ID )
          )
          AND ( wp_posts.post_type = 'post' )
          AND ( wp_posts.post_status = 'publish' );";

$posts = $wpdb->get_results($query);

foreach ($posts as $post) {
    //your post content here
}

Notice the FIND_IN_SET, that's where the magic happens.

Now... since I'm using SELECT * this returns all the post data and within the foreach you can echo out what you want from that (do a print_r($posts); if you don't know what's included. It doesn't set up "the loop" for you (I prefer it this way), but it can easily be modified to set up the loop if you prefer (take a look at setup_postdata($post); in the codex, you'll probably need to change SELECT * to select only post ID's and $wpdb->get_results to the correct $wpdb type -- see the codex for $wpdb also for information on that subject).

Whelp, it took a bit of effort, but since wp_query doesn't support doing 'compare' => 'IN' serialized or comma separated values this shim is your best option!

Hope this helps someone.

If you use the like comparison operator in your meta query, it should work fine to look inside a serialized array.

$wp_user_search = new WP_User_Query(array(
    'meta_query' => array(
        array(
            'key'     => 'wp_capabilities',
            'value'   => 'subscriber',
            'compare' => 'not like'
            )
        )
    )
);

results in:

[query_where] => WHERE 1=1 AND (
  ( wp_usermeta.meta_key = 'wp_capabilities' 
  AND CAST(wp_usermeta.meta_value AS CHAR) NOT LIKE '%subscriber%' )

If my meta data is array type, i'm use this method for query by meta:

$args = array(
    'post_type' => 'fotobank',
    'posts_per_page' => -1,
    'meta_query' => array(
            array(
                   'key' => 'collections',
                   'value' => ':"'.$post->ID.'";',
                   'compare' => 'LIKE'
            )
     )
);
$fotos = new WP_Query($args);
  • This could lead to unwanted results when a post ID has the same value as the id of the serialized string – Erenor Paz Apr 13 '17 at 10:05

I got curious about the answers above, where the meta_query targeted the key latitude instead of _coordinates. Had to go and test if it really was possible in meta queries to target a specific key inside a serialized array. :)

That obviously wasn't the case.

So, note that the correct key to target is _coordinates instead of latitude.

$args = array(
     'post_type' => 'my-post-type',
     'meta_query' => array(
         array(
             'key' => '_coordinates',
             'value' => sprintf(':"%s";', $value),
             'compare' => 'LIKE'
         )
     )
 );

NOTES:

  1. This approach makes it only possible to target exact matches. So things like all latitudes greater than 50 are not possible.

  2. To include substring matches, one could use 'value' => sprintf(':"%%%s%%";', $value),. (haven't tested)

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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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