how to fire join query with post_meta

Question

I have print sql query that runs within 'posts_where' filter and its like:

SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'shop_order' AND post_date >= '2013-08-26' AND post_date < '2013-10-28' AND post_date_gmt NOT LIKE '0000%'  ORDER BY wp_posts.post_date DESC 

now here I want to add where condition for post_meta value, so how to do that?

All data with post data and post_meta_data here:

$metadata=get_post_meta($post->ID,'order_data');
echo "<pre>";print_r($metadata);exit;

Array
(
    [0] => Array
        (            
            [billing_first_name] => tetst
            [billing_last_name] => test
            [billing_company] => 
            [billing_address_1] => 1701,5120 25th St N
            [billing_address_2] => 
            [billing_city] => Irvington
            [billing_postcode] => 07111-4501
            [billing_country] => AF
            [billing_state] => NJ
            [billing_email] => [email protected]
            [billing_phone] => (973) 351-2932
            [shipping_first_name] => tetst
            [shipping_last_name] => test
            [shipping_company] => 
            [shipping_address_1] => 1701,5120 25th St N
            [shipping_address_2] => 
            [shipping_city] => Irvington
            [shipping_postcode] => 07111-4501
            [shipping_country] => AF
            [shipping_state] => NJ
            [shipping_method] => 
            [shipping_service] => 
            [payment_method] => paypal
            [payment_method_title] => PayPal
            [order_subtotal] => 10.00
            [order_discount_subtotal] => 40
            [order_shipping] => 30.00
            [order_discount] => 0.00
            [order_tax] => 
            [order_tax_no_shipping_tax] => 0
            [order_tax_divisor] => 100
            [order_shipping_tax] => 0.00
            [order_total] => 40.00            
            [order_tax_total] => 0.00
            [assignto] => 7
            [delivery_day_time] => 2013-09-12 01:30
        )

)

without passing key in get_post_meta its look like:

$metadata=get_post_meta($post->ID);
echo "<pre>";print_r($metadata);exit;

[order_data] => Array
        (
            [0] => a:38:{s:22:"order_discount_coupons";a:0:{}s:18:"billing_first_name";s:5:"tetst";s:17:"billing_last_name";s:4:"test";s:15:"billing_company";s:0:"";s:17:"billing_address_1";s:19:"1701,5120 25th St N";s:17:"billing_address_2";s:0:"";s:12:"billing_city";s:9:"Irvington";s:16:"billing_postcode";s:10:"07111-4501";s:15:"billing_country";s:2:"AF";s:13:"billing_state";s:2:"NJ";s:13:"billing_email";s:7:"[email protected]";s:13:"billing_phone";s:14:"(973) 351-2932";s:19:"shipping_first_name";s:5:"tetst";s:18:"shipping_last_name";s:4:"test";s:16:"shipping_company";s:0:"";s:18:"shipping_address_1";s:19:"1701,5120 25th St N";s:18:"shipping_address_2";s:0:"";s:13:"shipping_city";s:9:"Irvington";s:17:"shipping_postcode";s:10:"07111-4501";s:16:"shipping_country";s:2:"AF";s:14:"shipping_state";s:2:"NJ";s:15:"shipping_method";s:15:"";s:16:"shipping_service";s:15:"";s:14:"payment_method";s:6:"paypal";s:20:"payment_method_title";s:6:"PayPal";s:14:"order_subtotal";s:5:"10.00";s:23:"order_discount_subtotal";d:40;s:14:"order_shipping";s:5:"30.00";s:14:"order_discount";s:4:"0.00";s:9:"order_tax";s:74:"zero_rate:amount^0,rate^0,compound^,display^Tax,^0";s:25:"order_tax_no_shipping_tax";d:0;s:17:"order_tax_divisor";i:100;s:18:"order_shipping_tax";s:4:"0.00";s:11:"order_total";s:5:"40.00";s:39:"order_total_prices_per_tax_class_ex_tax";a:1:{s:18:"";d:10;}s:15:"order_tax_total";s:4:"0.00";s:8:"assignto";s:1:"7";s:17:"delivery_day_time";s:16:"2013-09-12 01:30";}
        )

now here I want to apply where condition with 'delivery_day_time' field,

how to do that?

UPDATED

now am getting meta data as:

[order_assign_data] => Array
        (
            [0] => 7
        )

    [order_assign_date] => Array
        (
            [0] => 2013-09-28 02:30
        )

now how to apply condition?

Answer 1

Milo told you the answer. You need to store your data as individual keeps in the database rather than as a serialized array. MySQL cannot natively parse a serialized array though I have seen very complicated SQL that will. Still, that code is not going to be useful on a production server. It is too complicated and would be too slow. So...

... is there any possibility with present case?

... if you mean "Can I make this work with the serialized data I have?" then "no", not really. You have to change how the data is stored in the database. If you have already done that-- perhaps that is what the edit means, I can't quite tell-- then what you need is a JOIN.

SELECT 
  wp_posts.* 
FROM wp_posts 
LEFT JOIN wp_postmeta
  ON wp_postmeta.post_id = wp_posts.ID
WHERE 1=1  
AND wp_posts.post_type = 'shop_order' 
AND post_date >= '2013-08-26' 
AND post_date < '2013-10-28' 
AND post_date_gmt NOT LIKE '0000%'  
AND wp_postmeta.meta_key = 'whatever'
AND wp_postmeta.meta_value = 'somevalue'
ORDER BY wp_posts.post_date DESC

The logic and the SQL for joins can get complicated so you may need a lot more code than that.

And you should probably consider using WP_Query with a meta_query argument instead of your hand-rolled SQL.

At the very least use $wpdb->posts and $wpdb->postmeta instead of the hard-coded table names. What you've got is not portable.