CODEWITHSUNDEEP
mysqlsqlwordpressjoinwoocommerce
M Ali Salim
M Ali Salim

Reputation: 129

MySQL Getting SKU from WooCommerce Orders

I am using SQL Query to get the _sku of product. and this is my Query. But i am getting NULL in response. Can anyone point out what am i missing? Basically using this way i could get any product meta with the order details.

select 
p.ID as order_id, 
p.post_date, 
max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN oim.meta_key = '_product_id' and oim.order_item_id = oi.order_item_id  THEN oim.meta_value END ) as product_id,
max( CASE WHEN pm.meta_key = '_sku' and p.ID = oim.meta_value THEN pm.meta_value END ) as sku,
( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from 
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
join wp_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id
group by 
p.ID

Upvotes: 2

Views: 3299

Answers (2)

M Ali Salim
M Ali Salim

Reputation: 129

I have worked around with the query and found the answer, i added another join for post meta which is not connected to post meta of order id, so i needed seperate join to fetch product metas. so this what i did. Using this way you can get all the metas of purchased product.

select 
p.ID as order_id, 
p.post_date, 
max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN oim.meta_key = '_product_id' and oim.order_item_id = oi.order_item_id  THEN oim.meta_value END ) as product_id,
max( CASE WHEN opm.meta_key = '_sku' and oim.meta_value = opm.post_id THEN opm.meta_value END ) as sku,
( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items

from 
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
join wp_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id
join wp_postmeta opm on opm.post_id = oim.meta_value
group by 
p.ID

Upvotes: 2

Jack Robson
Jack Robson

Reputation: 2302

This looks way too complicated to me. I'm not sure what you're trying todo.

If the database isn't totally massive (100k+ posts), you could probably get away with writing a short script for this.

$file = fopen("skus.csv","w");

$orders = wc_get_orders();

foreach ( $orders as $order ) {
  $items = $order->get_items();

  foreach ( $items as $single_item ) 
    fputcsv($file, get_post_meta( $single_item->ID, '_sku', TRUE) );

}

Alternatively, if you have to stay inside the database, split your command into three parts.

Have the first part insert the results into a fresh table in a fresh table.

Then do the other parts from their.

Upvotes: 0

Related Questions