CODEWITHSUNDEEP
mysqlsqlwordpresswoocommerce
Andrej Gjurin
Andrej Gjurin

Reputation: 75

SQL for extracting SKU from order items (Woocommerce - Wordpress)

select
'EU' as country,
pmo.post_date,
pmo.ID,
p.order_id,
p.order_item_id,
p.order_item_name,
p.order_item_type,

max( CASE WHEN pm.meta_key = '_product_id' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as productID,
max( CASE WHEN pm.meta_key = '_qty' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as Qty,
max( CASE WHEN pm.meta_key = '_variation_id' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as variationID,
max( CASE WHEN pm.meta_key = '_line_total' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as lineTotal,
max( CASE WHEN pm.meta_key = '_line_subtotal_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subTotalTax,
max( CASE WHEN pm.meta_key = '_line_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as Tax,
max( CASE WHEN pm.meta_key = '_tax_class' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as taxClass,
max( CASE WHEN pm.meta_key = '_line_subtotal' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subtotal,
max( CASE WHEN pm.meta_key = 'jackpot_product' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as jprod

from

wpx7_woocommerce_order_items as p,
wpx7_woocommerce_order_itemmeta as pm,
wpx7_posts as pmo
 where order_item_type = 'line_item' and
 p.order_item_id = pm.order_item_id and
  p.order_id = pmo.id and  pmo.post_date > SUBDATE(CURRENT_DATE, 10)
 group by
p.order_item_id

I can not get my head around how I can successfully get the SKU metadata from postmeta... Please help If I try to join the postmeta table, I get an error and the the Query fails, and the site drops down... So then I must stop the faulty query...

Upvotes: 1

Views: 719

Answers (1)

Delonix
Delonix

Reputation: 31

This query worked for me:

SELECT p.ID, p.post_title, 
MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,
MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku
FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)                 
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
GROUP BY p.ID, p.post_title

Upvotes: 2

Related Questions