Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Sql query to exclude meta_value where meta_key = x on SUM for WooCommerce

Using Get orders total purchases amount for the day in Woocommerce answer code, returns the total value of woocommerce orders which works fine, but the problem is if I want to exclude all the orders where _billing_first_name key has not abc value, like in this code attempt:

global $wpdb;

return $wpdb->get_var( "
    SELECT DISTINCT SUM(pm.meta_value)
    FROM {$wpdb->prefix}posts as p
    INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
    WHERE p.post_type LIKE 'shop_order'
    AND p.post_status IN ('wc-processing','wc-completed')
    AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))
    AND pm.meta_key LIKE '_order_total'
    AND NOT (pm.meta_value = 'abc')
" );

I have tried a number of ways with no luck, any help is appreciated

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    For anyone looking into this the fix i made is:

    global $wpdb;

return $wpdb->get_var( "
    SELECT DISTINCT SUM(pm.meta_value)
    FROM {$wpdb->prefix}posts as p
    INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
    WHERE p.post_type LIKE 'shop_order'
    AND p.post_status IN ('wc-processing','wc-completed')
    AND pm.meta_key LIKE '_order_total'
    AND pm.post_id not in(SELECT post_id from wp_postmeta WHERE meta_key = '_billing_first_name' AND meta_value ='abc' )
" );

  2. 0

    You can also use 2 INNER JOIN for the same table with a different reference as following (to avoid a double query like in your answer):

    global $wpdb;

return $wpdb->get_var( "
    SELECT SUM(pm.meta_value)
    FROM {$wpdb->prefix}posts as p
    INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
    INNER JOIN {$wpdb->prefix}postmeta as pm2 ON p.ID = pm2.post_id
    WHERE p.post_type = 'shop_order'
    AND p.post_status IN ('wc-processing','wc-completed')
    AND pm.meta_key = '_order_total'
    AND pm2.meta_key = '_billing_first_name' 
    AND pm2.meta_value != 'abc'
" );

    Tested and works smoother.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search