php - Sql query to exclude meta_value where meta_key = x on SUM for WooCommerce
Get the solution ↓↓↓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
Answer
Solution:
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.
Answer
Solution:
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' )
" );
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: method illuminate\database\eloquent\collection::paginate does not exist.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.