Handy Woocommerce SQL queries

This post is just a place to store handy SQL queries for Woocommerce.

Get Order and all its information

select
    p.ID as order_id,
    p.post_date,
    pm.*
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
    p.ID = 14223

Generate an Orders report and specific information about the orders (eg things like order date, shipping address etc)

select
   p.ID as order_id,
   p.post_date,
   pm.*
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 p.id > 50457
    and pm.meta_key in (    
'_kookeli_tracking_number','_billing_email','_billing_first_name','_billing_last_name','_billing_city','_billing_country','_billing_phone','_order_total','_shipping_address_index','_payment_method_title',
        '_completed_date')        
        group by p.id, pm.meta_key

Get Users and billing and shipping addresses in Woocommerce

SELECT u.id, u.user_login, u.user_email, 

max( CASE WHEN m.meta_key = 'billing_email' and u.ID = m.user_id THEN m.meta_value END   ) as billing_email,

    max( CASE WHEN m.meta_key = 'billing_first_name' and u.id = m.user_id THEN m.meta_value END ) as billing_first_name,
    max( CASE WHEN m.meta_key = 'billing_last_name' and u.id = m.user_id THEN m.meta_value END ) as billing_last_name,
    max( CASE WHEN m.meta_key = 'billing_address_1' and u.id = m.user_id THEN m.meta_value END ) as billing_address_1,
    max( CASE WHEN m.meta_key = 'billing_address_2' and u.id = m.user_id THEN m.meta_value END ) as billing_address_2,
    max( CASE WHEN m.meta_key = 'billing_city' and u.id = m.user_id THEN m.meta_value END ) as billing_city,
    max( CASE WHEN m.meta_key = 'billing_state' and u.id = m.user_id THEN m.meta_value END ) as billing_state,
    max( CASE WHEN m.meta_key = 'billing_postcode' and u.id = m.user_id THEN m.meta_value END ) as billing_postcode,
    max( CASE WHEN m.meta_key = 'shipping_first_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_first_name,
    max( CASE WHEN m.meta_key = 'shipping_last_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_last_name,
    max( CASE WHEN m.meta_key = 'shipping_address_1' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_1,
    max( CASE WHEN m.meta_key = 'shipping_address_2' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_2,
    max( CASE WHEN m.meta_key = 'shipping_city' and u.id = m.user_id THEN m.meta_value END ) as shipping_city,
    max( CASE WHEN m.meta_key = 'shipping_state' and u.id = m.user_id THEN m.meta_value END ) as _shipping_state,
    max( CASE WHEN m.meta_key = 'shipping_postcode' and u.id = m.user_id THEN m.meta_value END ) as _shipping_postcode

FROM wp_users u
LEFT JOIN wp_usermeta m
ON  u.ID = m.user_id
group by u.ID

this bit of sql is based on this post at http://codecharismatic.com/

Get Order Objects (not strictly sql, but seems a good place to drop it)

    global $woocommerce;						
    $args = array(  'post_type'=> 'shop_order', 
                    'post_status' => 'publish', 
                    'posts_per_page' => -1, 
                    'tax_query' => array(  
                                            array(  'taxonomy' => 'shop_order_status',
                                                    'field' => 'slug',
                                                    'terms' => array('processing')
                                                )
                                        )
                  );	
    
    $orders = array();
    

    $loop = new WP_Query( $args );						
    while ( $loop->have_posts() ) : $loop->the_post(); 

        	$order_id = $loop->post->ID;								
                $order = new WC_Order($order_id);
                
                
                echo "id order = ".$order_id;
                
                $orders[] = $order;
    
    
    endwhile;
    
    
    
    return $orders;