Magento – Getting All Orders with Products Having a Certain Attribute Set

orders

Is there a way to get all orders from the database which have items with attribute set "X"? I know there is sales_flat_order_item and this might be a good point to start, but somehow I am stuck here.

Thanks for helping!

Best Answer

Option 1. Multiple queries - Not the best one

$attributeSetId = 4; //replace with own attribute set.
$productIds = Mage::getModel('catalog/product')->getCollection()
              ->addAttributeToSelect('attribute_set_id', $attributeSetId)
              ->getAllIds();

Now in $productIds you have all the products ids from attribute set 4. If this is an empty array then it's clear you will not have any orders for this attribute set and you don't need to go on.

If it's not empty....

$orderItsms = Mage::getModel('sales/order_item')->getCollection()
        ->addFieldToFilter('product_id', array('in'=>$productIds));

$orderIds = array();
foreach ($oderItems as $item) {
    $orderIds[$item->getOrderId()] = 1;
}
$orderIds = array_keys($orderIds);

now get the order objects.

$orders = Mage::getModel('sales/order')->getCollection()
        ->addFieldToFilter('entity_id', array('in'=>$orderIds));

I'm sure there is a way to optimize the queries that run, but this should work.

Option 2. With a query - if you need a fast look in the db
And here is a query, just for the fun of it.

SELECT 
    o.*
FROM
    sales_flat_order o
LEFT JOIN
        sales_flat_order_item i 
        ON o.entity_id = i.order_id
LEFT JOIN
        catalog_product_entity p
        ON i.product_id = p.entity_id
WHERE
    p.attribute_set_id = 4 
GROUP BY o.entity_id

Option 3. One select - clean.
Based on the query above I realized you can get the list of orders with a single select built the magento way. 

$attributeSetId = 4;
$collection = Mage::getModel('sales/order')->getCollection();
$collection->join(
    array('i'=>'sales/order_item'),
    'order_id = entity_id',
    array()
);
$collection->join(
    array('p'=>'catalog/product'),
    'i.product_id = p.entity_id',
    array()
);
$collection->getSelect()->where('p.attribute_set_id = ?', $attributeSetId);
$collection->getSelect()->group('main_table.entity_id');

Then just do foreach loop on the $collection and you will have the orders.

Related Topic