0

If I get my posts using meta_query by 'my_meta_key' only I get something like this

Array(
    [0] => Array(
       [nested_key1] => something_I_want1
       [nested_key2] => something_I_want2
       [dont_care_key] => something_I_dont_care_about
    )
    [1] => Array(
       [nested_key1] => something_I_want1
       [nested_key2] => something_I_DONT_want
       [dont_care_key] => something_I_dont_care_about
    )
)

Now I would like to do a meta_query, that would only fetch posts with 'my_meta_key' containing something_I_want1 AND something_I_want2 at the same time...

Problem is, I can't query by the full array, because a) I really don't care about "something_I_dont_care_about" metas and b) I don't know what they will be - so I need to query only by partial array...

I've tried this without success (WP_Query meta_query arg) (no posts fetched):

'meta_query' => [
  [
    'key' => 'my_meta_key',
    'value' => [
      'nested_key1' => 'something_I_want1',
      'nested_key2' => 'something_I_want2',
    ],
    'compare' => 'IN', //I've tried with and without  
  ]
],

What am I doing wrong? Is it even possible to query only by part of meta array?

  • What is the value you are storing in my_meta_key, can you post a sample? What do you mean with "nested meta key"? – cybmeta Apr 12 '16 at 14:22
  • @cybmeta well that is shown in the first code :) The value of my_meta_key meta is actually an array - I need to test some parts of that array :) – jave.web Apr 12 '16 at 14:32
  • Sorry but I can not know if it is the value stored in my_meta_key, specially if you prepend the sample code with "I get something like this". Anyway, you shouldn't store arrays in database, maybe PHP serialized arrays in some situations, but never if you need to filter by some of the values inside that array. It will be a very tricky job. – cybmeta Apr 12 '16 at 14:44
  • 2
    No, it can not do it as you can not do it with SQL as well. The most you can do it to get all meta fields with your meta key and check the values in PHP, or maybe worst, a LIKE string search. Maybe this and this can help you. – cybmeta Apr 12 '16 at 15:21
  • 3
    @cybmeta as an answer please, good explanation why this is not properly possible is valuable too. – Rarst Apr 12 '16 at 15:46
1

Fields in database tables can not contain other types of data than text strings and numbers, and variations of them like dates and times.

The field meta_value of wp_postmeta is a text field. So, when you store an array as meta_value you are really storing a string containing the serialized data that represents the original array. If you need to interpret that string again as array, you need to convet it back to that format in the appropiate language.

For example, you can get the string containing the array data in PHP, unserialized it and then searh in that array. If you know the structure of the string, you can also analyze it and cast fragments directly on a SQL statement.

But any of those methods are appropriate to filter or search by values of the array just because that array in SQL does not exist, it is a string, and to get the desired results you need to do a tricky job and consume huge resources.

It is a SQL limitation and, beacuse of that, it is a limitation of WP_Query as well.

That doesn't mean that serialized data are not appropiated to be stored in database at all. It is appropieated and commonly used for data that are NOT intented to be used for filtering, searching, ordering by or in any other way that is not just display the information.

If you need to perform some of those actions, you should store each array key in its own meta field. If you need to convert the already stored serialized data, you can use somthing like this (source):

$args = array(
    'post_type' => 'my_post_type',
    'meta_key' => 'my_meta_key',
    'posts_per_page' => -1
 );
$query = new WP_Query( $args );
if($query->have_posts()){
    while($query->have_posts()){
        $query->the_post();
        $my_meta_key = get_post_meta( get_the_ID(), 'my_meta_key', true );
        add_post_meta( get_the_ID(), 'nested_meta_key_1', $my_meta_key['nested_meta_key_1']);
        add_post_meta( get_the_ID(), 'nested_meta_key_2', $my_meta_key['nested_meta_key_2'] );
        delete_post_meta( get_the_ID(), 'my_meta_key', $my_meta_key );
    }
}
  • Well I am retrieving 3rd party data, so how the values are stored is actually not on me :) In the end I solved it as you write in the begining of your answer - fetching all by the key and going through them (not much big data, so it was OK approach here... ) Thanks anyway ! :) – jave.web Apr 12 '16 at 18:59
  • I don't understand how you use WP_Query to "retrieve" 3td party data; I assume you are retrieving it, storing it locally in your database and then querying it with WP_Query. It the situation is something like that, there is nothing that stop you to store the data in a more appropiate way for SQL queries. – cybmeta Apr 13 '16 at 5:06
  • 3rd party plugin stores data this way, I really just retrieve them :) So again, I can not really modify the way the data are stored (I would have to re-program the plugin) – jave.web Apr 13 '16 at 5:23
  • Ahhh, OK. I understand. – cybmeta Apr 13 '16 at 5:44

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.