TransWikia.com

select product id & product quantity from orders

WordPress Development Asked on February 22, 2021

I necessary get all products with order_id, order_date, product_id, product_quantity . But with query below i get two rows for 1 product: for _qty and _product_id . I need one row.

    $posts_table = $wpdb->posts;

    SELECT p.ID, p.post_date, i.order_item_id, im.meta_value, im.meta_key
    FROM $posts_table as p
    INNER JOIN wp_woocommerce_order_items as i
    ON p.ID = i.order_id
    INNER JOIN wp_woocommerce_order_itemmeta AS im
    ON im.order_item_id = i.order_item_id
    WHERE p.post_type='shop_order' AND (p.post_status='wc-completed' OR p.post_status='wc-delivered' OR p.post_status='wc-processing' OR p.post_status='wc-processing-noinvoice' OR p.post_status='wc-closed') AND i.order_item_type!='shipping' AND (im.meta_key='_qty' OR im.meta_key='_product_id')
    ORDER BY p.ID ASC

I get:

ID post_date order_item_id meta_value meta_key
55 2020.12.20 1 3 _qty

and

ID post_date order_item_id meta_value meta_key
55 2020.12.20 1 5 _product_id

I need:

ID post_date order_item_id _product_id _qty
55 2020.12.20 1 5 3

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP