Magento – Magento product collection ignores custom sort order (order by FIELD)

collection;customproductsql

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?

Best Answer

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);
Related Topic