0

I've setup this collection to sort products based on a list i have.

    $_productCollection = Mage::getModel('catalog/product')->getCollection();
    $_productCollection->addAttributeToSelect('*');
    $_productCollection->addUrlRewrite();
    $_productCollection->addAttributeToFilter('visibility', 4);
    $_productCollection->addStoreFilter(Mage::app()->getStore()->getStoreId());
    $_productCollection->addAttributeToFilter('sku', array('in' => $this->_productskus));
    Mage::getModel('review/review')->appendSummary($_productCollection);    
    $_productCollection->getSelect()->order(new Zend_Db_Expr('FIELD(e.sku, ' . "'" . implode("','", $this->_productskus) . "'".')'));

When i print the SQL just before looping "foreach" it gives me a nice working SQL statement (tested this in db). Order is good too.

 .....   AND (e.sku IN('ABC123', 'TEST', 'ATEST')) ORDER BY FIELD(e.sku, 'ABC123', 'TEST', 'ATEST')

However, when i loop over the collection, the order is not right!

After enabling SQL debugging all queries. It seems the product collection SQL has cut of the order-by part.

Any ideas why and where magento is doing this?

2

The problem appears because of appendSummary call.
If you take a look inside the method you will see this:

    $entityIds = array();
    foreach ($collection->getItems() as $_itemId => $_item) {
        $entityIds[] = $_item->getEntityId();
    }

and when calling $collection->getItems() the collections is loaded from the db (sql is executed).

Everything you change in the collection object's sql after loading the collection is just like like my college degree....useless.
it has no effect on the collection itself.
even if the sql changed the items are already loaded and won't be loaded again.

You can try first to add your sorting and after that the appendSummary

Something like this:

...
$_productCollection->addAttributeToFilter('sku', array('in' => $this->_productskus));
$_productCollection->getSelect()->order(new Zend_Db_Expr('FIELD(e.sku, ' . "'" . implode("','", $this->_productskus) . "'".')'));
Mage::getModel('review/review')->appendSummary($_productCollection);    

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.