WP_Query when meta_value saved as serialized array

Question

I am fetching woocommerce products manually.

The problem is that i have a custom field on products i.e _locations. A product can belong to multiple locations so i provided a multi select list in the product adding form in wp-admin.

Below is the function by which i am saving the meta_value

function woo_add_custom_general_fields_save( $post_id ){        
    // Select
    $woocommerce_select = $_POST['_locations'];
    if( !empty( $woocommerce_select ) )
        update_post_meta( $post_id, '_locations', esc_attr(maybe_serialize($woocommerce_select )) );
}

Notice i have serialzed the data for meta_value so that i have only one unique key _locations with all locations values associated with it.

Now the problem occurs when i am fetching products

$args = array(
      'post_type' => 'product',
      'product_cat' => $food_category->slug,
      'meta_key' => '_locations', 
      'meta_value' => 'newyork'   
     );
     $loop = new WP_Query($args);

I want to get products only for newyork but in the database it is stored as serialzed array

s:69:"a:2:{i:0;s:7:&quot;newyork&quot;;i:1;s:13:&quot;massachusetts&quot;;}";

How can i make this query fetch only newyork products.

Thanks

score 5 · Accepted Answer · answered Oct 14 '14 at 21:23

5

try:

$args = array(
  'post_type' => 'product',
  'product_cat' => $food_category->slug,
  'meta_query' => array(
     array(
        'key'     => '_location',
        'value'   => '%newyork%',
        'compare' => 'LIKE',
     ),
   ),
 );

answered Oct 14 '14 at 21:23

David

5,897
3
24
43

Yup its helpful :) Thanks. But do you think this is how it should be done ? I wouldn't mind any other approach but upto the standard of wordpress. – Raheel Oct 17 '14 at 09:23
yes, wp query is just a wrapper for php mysql functions, its still cachable, etc! – David Oct 17 '14 at 11:19
2

in my experience, I think % is automatically added so no need to write %newyork% – Luca Reghellin Nov 28 '16 at 15:20
As a matter of fact, right now if I use the `%` **it doesn't work** – brasofilo Jul 26 '22 at 14:00
This answer is very old and I haven't worked with WP since about this time, but from memory, the % character only worked with the like operator. It may well be that later versions added in the % operator for the like operator. There is a way to output the sql query if you research it. – David Jul 26 '22 at 21:40

score 2 · Answer 2 · answered Apr 25 '17 at 04:46

I had a similar problem and I solved it using the serialize(). Below is the example.

$args = array(
  'post_type' => 'product',
  'product_cat' => $food_category->slug,
  'meta_query' => array(
     array(
        'key'     => '_location',
        'value'   => serialize( array( 'newyork' ) ),
        'compare' => 'LIKE',
     ),
   ),
 );

score 0 · Answer 3 · answered Feb 24 '16 at 11:30

$args = array(
  'post_type' => 'product',
  'product_cat' => $food_category->slug,
  'meta_query' => array(
     array(
        'key'     => '_location',
        'value'   => '%"newyork"%', // note the quotes
        'compare' => 'LIKE',
     ),
   ),
 );

Use the quotes to avoid cases like: "newyorkcity", "newyorkcounty"...

It may not be the case if you store the names of the cities but you will have a big problem if you store for example the IDs for the cities.

Examples:

1. a:6:{i:0;s:3:"234";i:1;s:3:"539";i:2;s:3:"305";i:3;s:3:"541";i:4;s:3:"385";i:5;s:3:"588";}
2. a:2:{i:0;s:3:"182";i:1;s:3:"539";}
3. a:1:{i:0;s:3:"267";}

If you search for:

5, you will get examples 1 & 2, when you shouldn't get any results at all;
"5", you will get no results, as aspected

WP_Query when meta_value saved as serialized array

3 Answers3