CODEWITHSUNDEEP
phpmysqlwordpresswoocommerce
Jean-philippe Emond
Jean-philippe Emond

Reputation: 1614

(mysql) Get woocommerce Order by meta_key and product_id

I have a mysql issues.

I have this select

SELECT DISTINCT t2.meta_key,t2.meta_value
FROM wp_woocommerce_order_items AS t1
JOIN wp_woocommerce_order_itemmeta AS t2 ON t1.order_item_id = t2.order_item_id
JOIN wp_posts as t3 ON t3.ID=t1.order_id
WHERE t2.order_item_id IN(SELECT distinct t1.order_item_id FROM wp_woocommerce_order_items AS t1
JOIN wp_woocommerce_order_itemmeta AS t2 ON t1.order_item_id = t2.order_item_id WHERE t2.meta_key like "_product_id" AND t2.meta_value = 99) AND t2.meta_key="member_id" AND t3.post_status like "publish"

this Select query get all "member_id"(meta_key) where the "_product_id" equals 99.

I hav3 a good result. it's maybe not "elegant" but it's works.

My problem now, I need to get all member_id where the _product_id equal 99 AND the Order is not cancelled.

Also, I don't know which table and what is the real key that "order_status" are.

So,

Thanks For you help!

EDIT

I added the 

JOIN wp_posts as t3 ON t3.ID=t1.order_id

and

WHERE t3.post_status = "publish"

The current value that I need is in the table: wp_term.

thanks

Upvotes: 0

Views: 3201

Answers (1)

Jean-philippe Emond
Jean-philippe Emond

Reputation: 1614

ok, I got it.

This query take all "meta_value" (here: member_id)

  • On all orders where
    • Order status is not : "failed' 'cancelled' or 'refunded'
    • where order have the product id is 99
    • Where the order is not into the trash.

so :

SELECT t2.meta_value
FROM wp_woocommerce_order_items AS t1
JOIN wp_woocommerce_order_itemmeta AS t2 ON t1.order_item_id = t2.order_item_id
WHERE t2.order_item_id IN(SELECT DISTINCT oi.order_item_id
FROM wp_posts, wp_postmeta, wp_term_relationships tr,wp_term_taxonomy tt,
     wp_terms te, wp_woocommerce_order_itemmeta oi, wp_woocommerce_order_items ii 
WHERE tt.taxonomy like "shop_order_status"
AND te.term_id = tt.term_id
AND te.slug  NOT IN ('failed','cancelled','refunded')
AND tr.term_taxonomy_id = tt.term_taxonomy_id
AND oi.order_item_id = ii.order_item_id
AND oi.meta_key = '_product_id'
AND oi.meta_value = 99
AND ii.order_id = wp_posts.ID
AND wp_postmeta.post_id = tr.object_id
AND wp_posts.post_status = 'publish') AND t2.meta_key like "member_id"

I hope to help someOne.

If you have more elegant or more optimisation of this query.

Feel free to tell us :-)

Upvotes: 1

Related Questions