0

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!

3

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.

0

Try this.

$attrSetName = 'your_attribute_set';
$attributeSetId = Mage::getModel('eav/entity_attribute_set')
    ->load($attrSetName, 'attribute_set_name')
    ->getAttributeSetId();

//Load product model collecttion filtered by attribute set id
$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addFieldToFilter('attribute_set_id', $attributeSetId);

$aProductIds = array();
foreach($products as $product){
    $aProductIds[] = $p->getId();
}
$ordersCollection = Mage::getResourceModel('sales/order_item_collection')
    ->addAttributeToFilter('product_id', array('in' => $aProductIds))
    ->load();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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