3

How to view orders for a specific product in WooCommerce using sku or product name? What I have so far is that, but it does not work.. Does anyone know how to do?

<?php
   $args = array(
    'post_type'       =>'shop_order',
    'post_status'    => 'publish',
    'posts_per_page' =>  50,
    'order'          => 'DESC',
    'item_meta' =>  array (
    '_sku' => 'ABCD',
    ),              
    'tax_query' => array( 
     array( 'taxonomy' => 'shop_order_status',
         'field' => 'slug',
         'terms' => array ('Pending' , 'Failed' , 'Processing' , 'Completed', 'On-Hold' , 'Cancelled' , 'Refunded')
        ) 
      )             
  );
?>             
<table id="tblExport"  class="demotable1" style="border:1px solid black; ">
            <thead>
                <tr>
                    <th ><?php _e('ID:', ' '); ?></th>
                    <th ><?php _e('sku:', ' '); ?></th>
                    <th ><?php _e('Categories:', ' '); ?></th>
                    <th ><?php _e('Product:', ' '); ?></th>
                    <th ><?php _e('Date:', ' '); ?></th>
                    <th ><?php _e('Value:', ' '); ?></th>
                    <th ><?php _e('Name:', ' '); ?></th>
                    <th ><?php _e('E-mail:', ' '); ?></th>
                    <th ><?php _e('status:', ' '); ?></th>
                </tr>
            </thead>
            <tbody id="export-pla"  >      
                <?php
                $loop = new WP_Query( $args  );
                while ( $loop->have_posts() ) : $loop->the_post(); 
                $order_id = $loop->post->ID; 
                $order = new WC_Order($order_id);
                ?> 
                <tr>
                     <td>
                        <?php
                             //ID - order
                            if ($order->id) : ?><?php echo $order->id; ?><?php endif;?>
                    </td>

                    <td>
                        <?php
                            //SKU
                            if (sizeof($order->get_items())>0)  { foreach($order->get_items() as $item) 
                            { $_product = get_product( $item['product_id'] );   echo '' . $_product->sku . '';   }  }
                        ?>
                    </td>
                    <td>
                        <?php
                            // Categories
                            if (sizeof($order->get_items())>0)  { foreach($order->get_items() as $item)
                            { $_product = get_product( $item['product_id'] ); 
                            echo $_product->get_categories( ', ', '' . _n( '', '', $size, 'woocommerce' ) . ' ', '  ' ); } }
                        ?>
                    </td>

                    <td>
                        <?php
                          // product name
                           if (sizeof($order->get_items())>0)   { foreach($order->get_items() as $item) 
                           { $_product = get_product( $item['product_id'] ); echo '' . $item['name'] . '';  } }
                        ?>
                    </td>

                    <td>
                        <?php echo the_time('d/m/Y'); ?>
                    </td>
                    <td>
                        <?php if ($order->order_total): $preco_format=($order->order_total);?>
                        <?php echo $trata_preco=number_format($preco_format, 2, ",", "."); ?><?php endif; ?>
                    </td>

                    <td>
                        <?php if ($order->billing_first_name) : ?><?php echo $order->billing_first_name; ?><?php endif; ?>
                        <?php if ($order->billing_last_name) : ?><?php echo $order->billing_last_name; ?><?php endif; ?>
                    </td>

                    <td>
                        <?php if ($order->billing_email) : ?><?php echo $order->billing_email; ?><?php endif; ?>
                    </td>


                    <td>
                        <?php if ($order->status) : ?><?php echo $order->status; ?><?php endif; ?>
                    </td>

               </tr> 
                   <?php endwhile; ?>
                   <?php wp_reset_query(); ?> 
            </tbody>
        </table>

closed as off-topic by Brad Dalton, Pieter Goosen, Howdy_McGee, Robert hue, Mark Kaplun Jul 30 '15 at 3:17

This question appears to be off-topic. The users who voted to close gave this specific reason:

If this question can be reworded to fit the rules in the help center, please edit the question.

  • Term slugs cannot have uppercase letters, slugs are always lowercase. Recheck your slugs and correct them accordingly – Pieter Goosen Jul 13 '15 at 6:21
  • Is this thing done. but it didn't work. I think the item_meta must be configured differently, but do not know how. – Thiago Jul 13 '15 at 19:40
-1

Eric based on your code I wrote to meet my needs, for those who want to follow:

Eric com base em seu código eu escrevi o para atender as minhas necessidades, para quem quiser segue abaixo:

<?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 );
                }
?>               
<table>
            <thead>
                <tr>
                    <th ><?php _e('ID do Pedido:', ''); ?></th>
                    <th ><?php _e('sku:', ''); ?></th>
                    <th ><?php _e('Categoria:', ''); ?></th>
                    <th ><?php _e('Produto:', ''); ?></th>
                    <th ><?php _e('Data Consolidada da compra:', ''); ?></th>
                    <th ><?php _e('Valor:', ''); ?></th>
                    <th ><?php _e('Nome:', ''); ?></th>
                    <th ><?php _e('Tel:', ''); ?></th>
                    <th ><?php _e('End:', ''); ?></th>
                    <th ><?php _e('Cidade:', ''); ?></th>
                    <th ><?php _e('Estado:', ''); ?></th>
                    <th ><?php _e('E-mail:', ''); ?></th>
                    <th ><?php _e('status:', ''); ?></th>
                </tr>
            </thead>
            <tbody>      
                <?php
                while ( $orders->have_posts() ) : $orders->the_post(); 
                $order_id = $orders->post->ID; 
                $order = new WC_Order($order_id);            
                ?> 
                <tr>
                     <td>
                        <?php
                           //  Exibe o ID do pedido 
                         if ($order->id) : ?>
                            <a href="<?php echo esc_url( home_url('/' ) ); ?>wp-admin/post.php?post=<?php echo $order->id; ?>&action=edit" target="_blank"><?php echo $order->id; ?></a><?php endif;?>
                    </td>
                    <td>
                        <?php
                            // Exibe o SKU
                            if (sizeof($order->get_items())>0)  { foreach($order->get_items() as $item) 
                            { $_product = get_product( $item['product_id'] );   echo '' . $_product->sku . '';   }  }
                        ?> 
                    </td>
                    <td>
                        <?php
                            // Exibe a Categoria do produto
                            if (sizeof($order->get_items())>0)  { foreach($order->get_items() as $item)
                            { $_product = get_product( $item['product_id'] ); 
                            echo $_product->get_categories( ', ', '' . _n( '', '', $size, 'woocommerce' ) . ' ', '  ' ); } }
                        ?>
                    </td>

                    <td>
                        <?php
                            // Exibe o nome do produto
                           if (sizeof($order->get_items())>0)   { foreach($order->get_items() as $item) 
                           { $_product = get_product( $item['product_id'] ); echo '' . $item['name'] . '';  } }
                        ?>
                    </td>                   
                    <td>
                        <?php 
                        // Exibe a data da copmpra
                         echo the_time('d/m/Y'); ?>
                    </td>
                    <td>
                        <?php 
                        // Exibe o valor da compra + a formatação do preço
                        if ($order->order_total): $preco_format=($order->order_total);?>
                        <?php echo $trata_preco=number_format($preco_format, 2, ",", "."); ?><?php endif; ?>
                    </td>
                    <td>
                        <?php 
                         // Exibe o nome e sobrenome do cliente
                         if ($order->billing_first_name) : ?><?php echo $order->billing_first_name; ?><?php endif; ?>
                        <?php if ($order->billing_last_name) : ?><?php echo $order->billing_last_name; ?><?php endif; ?>
                    </td>
                  <td>
                        <?php 
                         // Exibe o telefone do cliente
                         if ($order->billing_phone) : ?><?php echo $order->billing_phone; ?><?php endif; ?>
                    </td>
                    <td>
<?php // Exibe o endereço do cliente
 if ($order->billing_address_1): ?><?php echo $order->billing_address_1; ?>, <?php endif; ?> <?php if ($order->billing_number): ?> <?php echo $order->billing_number; ?><?php endif; ?> <?php if ($order->billing_address_2): ?><?php echo $order->billing_address_2; ?> <?php endif; ?><?php if ($order->billing_neighborhood): ?>Bairro: <?php echo $order->billing_neighborhood; ?><?php endif; ?> <?php if ($order->billing_postcode): ?>Cep: <?php echo $order->billing_postcode; ?><?php endif; ?>
                    </td>               
                    <td>
                        <?php 
                        // Exibe a cidade do cliente
                        if ($order->billing_city): ?><?php echo $order->billing_city; ?><?php endif; ?>
                    </td>
                    <td>
                        <?php 
                        // Exibe o estado do cliente
                        if ($order->billing_state): ?><?php echo $order->billing_state; ?><?php endif; ?>
                    </td>
                    <td>
                        <?php 
                        // Exibe o e-mail do cliente
                         if ($order->billing_email) : ?><?php echo $order->billing_email; ?><?php endif; ?>
                    </td>
                    <td>
                        <?php 
                        // Exibe o status do pedidodo cliente
                         if ($order->status) : ?><?php echo $order->status; ?><?php endif; ?>
                    </td>
               </tr> 

            <?php endwhile; ?>
                   <?php wp_reset_query(); ?> 
            </tbody>
        </table>
-2

Feito!

Realmente com o WP_Query não dá... pra saber essa informação é necessário utilizar 2 tabelas do WooCommerce.

Então montei a função pra você... ela busca pelo postID do produto e retorna o objeto dos pedidos pra você fazer o loop normal.

É só coloca-la no seu function.php e usa-la em qualquer lugar que quiser! Aqui funcionou bem! Se algo der errado ai, me fala que a gente vê!

function retorna_idpedidos_por_produtoid($produtoId) {
global $wpdb;
$tabelaOrderItemMeta = $wpdb->prefix . 'woocommerce_order_itemmeta';
$tabelaOrderItems = $wpdb->prefix . 'woocommerce_order_items';

$resultadoSelect = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT b.order_id
           FROM {$tabelaOrderItemMeta} a, {$tabelaOrderItems} b
          WHERE a.meta_key = '_product_id'
            AND a.meta_value = %s
            AND a.order_item_id = b.order_item_id
            ORDER BY b.order_id DESC",
        $produtoId
    )
);

if($resultadoSelect)
{
    $resultado = array();

    foreach($resultadoSelect as $item)
        array_push($resultado, $item->order_id);

    if($resultado)
    {
        $args = array(
            'post_type' =>'shop_order',
            'post_status' => 'publish',
            'posts_per_page' => -1,
            'post__in' => $resultado,
            'order' => 'DESC'
        );
        $query = new WP_Query($args);

        return $query;
    }
} }

Pronto, agora é só fazer o loop, por exemplo:

<?php $pedidos = retorna_idpedidos_por_produtoid(33); ?>
    <?php if($pedidos && $pedidos->have_posts()): ?>
        <?php while ($pedidos->have_posts()): $pedidos->the_post(); ?>
            <pre>
                <?php the_ID(); ?>
            </pre>
        <?php endwhile ?>
    <?php endif; ?>

Teste aí, espero que dê certo! Abs!

  • 3
    Please, use english when posting on WPSE. – cybmeta Jul 16 '15 at 6:53
  • Very good Ericc never could without their help, this bay do not select crossed my mind. Once again their expertise was crucial, it was perfect! That helped me a lot, I owe you that. Protugues Muito bom Ericc, jamais conseguiria sem sua ajuda, essa sacada de fazer o select nem passou pela minha cabeça. Mais uma vez seus conhecimentos foram cruciais, ficou perfeito! Isso me ajudou muito, fico te devendo essa. – Thiago Jul 22 '15 at 13:50

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