Magento: find orders containing a product

Question

Is there a way in Magento to find all orders that contain a given product? It would be even better if it could be done from the administration panel.

Reports -> Products -> Products Ordered gives me the day the product was sold and how many orders contain it, but I need to know which specific orders include the product.

Thank you!

Answer 1

I answered this question in another question; Get a list of orders in magento extension that have a certain product

For quick reference:

$productId = {PRODUCT_ID};
$orders = array();
$collection = Mage::getResourceModel('sales/order_item_collection')
    ->addAttributeToFilter('product_id', array('eq' => $productId))
    ->load();
foreach($collection as $orderItem) {
    $orders[$orderItem->getOrder()->getIncrementId()] = $orderItem->getOrder();
}

Answer 2

You can get by simple mysql query:-

select order_id from sales_flat_order_item where product_id=//given product id

OR

You can customize the reports according to your needs.

Answer 3

If you want to get the increment_id (the incremental Order Number) instead of the order_id, you can simply use this Statement:

SELECT o.increment_id 
FROM sales_flat_order_item oi 
INNER JOIN sales_flat_order o ON o.entity_id = oi.order_id 
WHERE product_id=XXX ORDER BY o.increment_id DESC;