Query Posts Based on Serialized Meta Value

Recently I was working on an "inventory management" system where we I had to work with lots of products and their variations in color, size, sku, upc code, dimensions, price & other related data. Yes, I took help of meta box to take inputs and store them on the meta table. Now, if I were to save one meta value to one row as many would suggest, I would end up with 30 different meta data row for one product only. So basically for 1000 products and their meta data would require me 30,000 rows. Frankly, speaking I didn't quite like the idea. I thought of using one meta data row for each product and that’s exactly when I thought of serializing each products meta data and save them in one row. So, I would end up with only 1000 rows on my meta data table.

Yes, it might sound a bit complicated but it's fairly easily and WP works just fine with data serialization. Here is a sample of one product's meta data on an array which I slimmed down a bit for better understanding.

Array(
   [sku] => SKU-BEL
   [cost] => 10
   [retail] => 20
   [notify] => on
   [height] => 6
   [width] => 7
   [length] => 8
   [depth] => 5
   [weight] => 4
   [volume] => 3
   [stock] => Array(
      [0] => Array(
          [color] => Red
          [sku] => SKU-BEL-RED
          [quantity] => 1
          [upc] => 1111111111111)
      [1] => Array(
          [color] => Blue
          [sku] => SKU-BEL-BLUE
          [quantity] => 5
          [upc] => 2222222222222)
      [2] => Array(
          [color] => Yellow
          [sku] => SKU-BEL-YELLOW
          [quantity] => 3
          [upc] => 3333333333333)
     )
)

As you can see from this array, I have lots of array keys and their values along with nested array. This array would be serialized first and only then it would be saved with a meta key (product_details) on our postmeta table in WordPress.

Now, if you take a close look at the array you would find a key "notify" which has a value "on". This came from a checkbox input which is one of our key to list all the products that has this value. You can easily query to list those products like this.

<?php
global $post;
$args = array(
   'post_type' => 'products',
   'post_status' => 'publish',
   'meta_key' => 'product_details',
   'nopaging' => true,
   'orderby' => 'modified',
   'order' => 'ASC');
$myposts = get_posts($args);
foreach ($myposts as $post) : setup_postdata($post); 
    // unserialize metadata
    $data = unserialize(get_post_meta($post->ID,'product_details',true));
    // check meta key value
    if($data['notify'] == 'on'){
        echo get_the_title();
    }
endforeach;
wp_reset_postdata();
?>

This is as simple as it can get. I understand that serialization may not be the best way to about it but hey as long as you can get your job done, where is the problem? I hope you would find this post useful. Please let us know your thoughts on this from the comment section below. Thank you.