Ram February 2016

How to create Woocoomerce order query with product name?

Hi i need to get all orders that belongs to or contain A PRODUCT. ie if product1 is my product name then i need to get all orders in the loop that contain this product . For to get order detail i use the following query

                      $args = array(

                    'post_type'         => 'shop_order',

                    'post_status'       => 'publish'
                         );
          $loop=new WP_Query($args); 
         while($loop->have_posts()): $loop->the_post(); 
         $order_id=get_the_ID();
         $order = new WC_Order($order_id);

        ..details i fetched from query ..


            endwhile ;

to get item details i can use $order->get_items(); I want to

(1) it is the best method to fetch order details in the loop ?

(2)How to fetch orders that belongs to product1 [where product1 is my product name ] .?

Is anyone know ,Please help .

Answers


PHPExpert February 2016

For first question yes you really need to think on it, for second question you can use the following,

<?php
  global $wpdb;   
  $produto_id = 22777; // ID do produto
  $consulta = "SELECT order_id FROM {$wpdb->prefix}woocommerce_order_itemmeta woim 
   LEFT JOIN {$wpdb->prefix}woocommerce_order_items oi 
    ON woim.order_item_id = oi.order_item_id 
    WHERE meta_key = '_product_id' AND meta_value = %d
    GROUP BY order_id;";
  $order_ids = $wpdb->get_col( $wpdb->prepare( $consulta, $produto_id ) );
  if( $order_ids ) {
             $args = array(
            'post_type'       =>'shop_order',
            'post__in'   => $order_ids,
            'post_status'    => 'publish',
            'posts_per_page' =>  20,
            'order'          => 'DESC',             
            'tax_query' => array( 
            array( 'taxonomy' => 'shop_order_status',
                   'field' => 'slug',
                   'terms' => array ('Pending' , 'Failed' , 'Processing' , 'Completed', 'On-Hold' , 'Cancelled' , 'Refunded')
                  ) 
                )           
            );
            $orders = new WP_Query( $args );
            }
?> 

For more information refer http://zjstech.com/basic-woocommerce-mysql-queries-for-orders-products-and-categories/

Post Status

Asked in February 2016
Viewed 1,319 times
Voted 10
Answered 1 times

Search




Leave an answer