Ask your WordPress questions! Pay money and get answers fast! (more info)

Querying Orders by Items/Product WordPress

  • SOLVED

Is there a way to query orders by the _product_id value stored in {prefix}_woocommerce_order_itemmeta?

I’ve tried:

$meta_query = array(
array('key' => '_product_id','value' => $product_id)
);

$orders = new WP_Query(array('post_type' => 'shop_order','meta_query' => $meta_query,'fields' => 'ids','nopaging' => true,'no_found_rows' => true));


Thanks!

Answers (2)

2014-04-11

Dbranes answers:

You can try the following:

global $wpdb;

$product_id = 423; // some input value

$sql = "
SELECT order_id FROM {$wpdb->prefix}woocommerce_order_itemmeta oim
LEFT JOIN {$wpdb->prefix}woocommerce_order_items oi
ON oim.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( $sql, $product_id ) );

if( $order_ids ) {

$args = array(
'post_type' => 'shop_order',
'post__in' => $order_ids,
);
$orders = new WP_Query( $args );

}


You could also display it via:

if( $order_ids ){
foreach ( $order_ids as $order_id ) {
$order = new WC_Order( $order_id );
printf( 'Order: ID: %d, Status %s <br/>', $order->id, $order->status );
}
}


ps: just be careful of the extra lines when copy/paste code from this site.


Nathanael Moody comments:

Are you sure your sql query is completely correct? I’ve removed all the spacing and it kills my script.

I was able to achieve what I wanted with this script:


$results = $wpdb->get_results( "
SELECT order_item_id
FROM {$wpdb->prefix}woocommerce_order_itemmeta
WHERE {$wpdb->prefix}woocommerce_order_itemmeta.meta_value = '{$post_id}' AND {$wpdb->prefix}woocommerce_order_itemmeta.meta_key = '_product_id';
" );


if (!empty($results)) {
$order_item_ids = array();

foreach ($results as $result) {
$order_item_ids[] = $result->order_item_id;
}

$order_item_ids = join(',',$order_item_ids);

$orders = $wpdb->get_results( "
SELECT order_id
FROM {$wpdb->prefix}woocommerce_order_items
WHERE {$wpdb->prefix}woocommerce_order_items.order_item_id IN ($order_item_ids);
" );

return $orders;

}else{
return false;
}



It seems as though you’re trying to combine the queries that I did, but yours isn‘t working.


Dbranes comments:

Mine is working and displays for example the following output for product id 423:

Order: ID: 528, Status pending
Order: ID: 529, Status completed
Order: ID: 2981, Status completed
Order: ID: 2982, Status completed
Order: ID: 2985, Status completed
Order: ID: 2986, Status processing


Maybe you can use this instead:

[[LINK href="http://"]]http://pastie.org/pastes/9071717/text[[/LINK]]

I did a copy/paste from this and it worked.

What kind of PHP Error did you get?

ps: you should use WP_DEBUG on your dev site.


Dbranes comments:

ps: the above link was empty, here it is again:

[[LINK href="http://pastie.org/pastes/9071717/text "]]http://pastie.org/pastes/9071717/text[[/LINK]]


Nathanael Moody comments:

Yes. That works! The only thing I’m trying to do now is make sure an order is completed, but I think I can figure that out by using wp_query & tax_query.

2014-04-11

Sarbartha Das answers:

Is this not working?