0

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?

3
  • your order data is a serialized array under a single meta key, they should be stored within individual meta keys if you want to efficiently query on them. serialized data is a php construct, MySQL has no ability to parse that data.
    – Milo
    Sep 12, 2013 at 6:27
  • Thanks Milo for your answer, is there any possibility with present case?
    – Er.KT
    Sep 12, 2013 at 6:40
  • @Milo I have updated my code. now please can you suggest how to apply condition?
    – Er.KT
    Sep 12, 2013 at 10:45

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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