WooCommerce - Query for ordered products

Question

I'm trying to obtain all orders for particular day/time and then order products using SQL (MySQL) as seen in the query below:

select 
    p.ID as order_id,
    p.post_date,
    i.order_item_name,
    max( CASE WHEN im.meta_key = '_product_id' and p.ID = im.order_item_id THEN im.meta_value END ) as Prod_ID
from 
    wp_posts as p,
    wp_postmeta as pm,
    wp_woocommerce_order_items as i,
    wp_woocommerce_order_itemmeta as im
where 
    p.post_type = 'shop_order'
    and p.ID = pm.post_id
    and p.ID = i.order_id
    and p.post_date BETWEEN '2016-01-14 00:00:00' AND '2016-01-14 23:59:59'
    and p.post_status = 'wc-processing'

It looks like when I try to query wp_woocommerce_order_itemmeta table for ordered products data I'm loosing connection to a database (connection timed out during a query).

Any clue what is going on?

Answer 1

Fixed query

select 
        p.ID as order_id,
        p.post_date,
        i.order_item_name,
        max( CASE WHEN im.meta_key = '_product_id' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as Prod_ID
    from 
        wp_posts as p,
        wp_postmeta as pm,
        wp_woocommerce_order_items as i,
        wp_woocommerce_order_itemmeta as im
    where 
        p.post_type = 'shop_order'
        and p.ID = pm.post_id
        and p.ID = i.order_id
        and p.post_date BETWEEN '2016-01-14 00:00:00' AND '2016-01-14 23:59:59'
        and p.post_status = 'wc-processing'

Explanation: Post ID has no direct relation to the order item meta

Answer 2

Wanted to update this, as I found it when building out my own reporting.

A better method is to do the proper inner joins, minimizing query times:

select 
    p.ID as order_id,
    p.post_date,
    i.order_item_name,
    max( CASE WHEN im.meta_key = '_product_id' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as Prod_ID
from 
    wp_posts as p
    inner join
    wp_postmeta as pm 
    on p.id = pm.post_id
    inner join
    wp_woocommerce_order_items as i 
    on p.id = i.order_id
    inner join 
    wp_woocommerce_order_itemmeta as im 
    on i.order_item_id = im.order_item_id
where 
    p.post_type = 'shop_order'
    and p.post_date BETWEEN '2017-04-29 00:00:00' AND '2017-04-30 23:59:59'
    and p.post_status = 'wc-processing';