Last active
April 22, 2024 09:22
-
-
Save lukecav/60836976190e2f1ccd8ab15564cbfe9c to your computer and use it in GitHub Desktop.
MySQL script to get all WooCommerce orders including metadata
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name, | |
max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name, | |
max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1, | |
max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2, | |
max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city, | |
max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state, | |
max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode, | |
max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name, | |
max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name, | |
max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1, | |
max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2, | |
max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city, | |
max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state, | |
max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode, | |
max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total, | |
max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax, | |
max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date, | |
( 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 | |
where | |
post_type = 'shop_order' and | |
post_date BETWEEN '2021-01-01' AND '2021-08-01' and | |
post_status = 'wc-completed' | |
group by | |
p.ID |
@Flioper & @iputuadi want to maybe post what you have so far so one can assist?
I want to get the value of the quantity in each order. I try to use the "_qty" parameter, but it doesn't show anything. Do you know how to get the quantity value for each order on woocommerce via a query?
With qty was a little harder, here i have this code, for me its works, shows everything :)
(select group_concat(woim.meta_value separator '
' ) from wp_woocommerce_order_items woi join wp_woocommerce_order_itemmeta woim on woi.order_item_id = woim.order_item_id where woim.meta_key = '_qty' and woi.order_id = oi.order_id) as qty,
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Yes sure. Thank you.