0

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?

  • This is kind of off-topic since it's about MySQL and WooCommerce but not specifically about WordPress. Maybe you're hitting a limit and the tables are too big or something - I'm definitely not a MySQL expert but maybe LIMIT your results to 1 or 2 and see if anything is returned. Another idea is to COUNT() the number of rows it expects to return. – Howdy_McGee Jan 15 '16 at 21:23
  • There are only 46 orders for that day so shouldn't be a problem. Anyway I will try to hit WooCommerce devs on WP forum then. – JackTheKnife Jan 15 '16 at 21:25
  • 1
    While you may only see 46 orders in the backend - it could be a malformed query that is returning more results than you expect which may be the core of the issue - just suggestions to rule out the little things. Good luck! – Howdy_McGee Jan 15 '16 at 21:41
2

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

  • 1
    very inefficient sql, you should join the itemmeta table instead of the cross join. should delete this answer – xnagyg Feb 8 at 21:03
  • In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'p.ID'; this is incompatible with sql_mode=only_full_group_by – Olaf Erlandsen May 11 at 5:04
0

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.