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?
2 Answers
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