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)
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.
