"random" order clause added to my collection

Question

For a module I load a custom product collection where I select products based on IDs in an array, I also try and sort the collection by the same order as the IDs have in the array().

To achieve the sorting I use Zend's getSelect->order() to add a custom "ORDER BY" to the SQL statement.

$collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('*')
            ->addAttributeToFilter('entity_id', $productIds)
            ->addOrder('entity_id');

$sortIds = array_map('intval', $productIds);
$collection->getSelect()
           ->order("find_in_set(e.entity_id,'".implode(',', $sortIds)."')");

This will generate a SQL error: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat_index_position' in 'order clause'", since I don't have the "cat_index_position" in my product flat table.

This error is because Magento adds an extra ORDER BY clause: 'cat_index_position' ASC after the one I add with getSelect()->order()

If I don't add the getSelect()->order() no extra ORDER BY is added and no SQL error is generated, however the sort order is obviously wrong.

I can, very ugly, hack this by commenting out the rest of the SQL statement like this $collection->getSelect()->order("find_in_set(e.entity_id,'".implode(',',$ids)."');--"); but I really really don't want to do that.

I need help tracking down the source of that extra ORDER BY clause or any other way of solving it.

UPDATE

If I do $collection->getSelect()->__toString() after I've added my custom order I get:

SELECT 1 AS status, e.entity_id, e.type_id, e.attribute_set_id
FROM catalog_product_flat_1 AS e
WHERE (((e.entity_id = '8534') OR (e.entity_id = '4184') OR (e.entity_id = '4185') OR (e.entity_id = '4183') OR (e.entity_id = '13314') OR (e.entity_id = '5069') OR (e.entity_id = '11217') OR (e.entity_id = '4355') OR (e.entity_id = '4354') OR (e.entity_id = '4353') OR (e.entity_id = '4352') OR (e.entity_id = '4351') OR (e.entity_id = '4350') OR (e.entity_id = '4349') OR (e.entity_id = '4348') OR (e.entity_id = '5203'))) ORDER BY find_in_set(e.entity_id,'8534,4184,4185,4183,13314,5069,11217,4355,4354,4353,4352,4351,4350,4349,4348,5203') ASC

And as I said previously, now Magento adds an extra ORDER BY, because the error I get say I have this SQL statement:

SELECT 1 AS status, e.entity_id, e.type_id, e.attribute_set_id
FROM catalog_product_flat_1 AS e
WHERE (((e.entity_id = '8534') OR (e.entity_id = '4184') OR (e.entity_id = '4185') OR (e.entity_id = '4183') OR (e.entity_id = '13314') OR (e.entity_id = '5069') OR (e.entity_id = '11217') OR (e.entity_id = '4355') OR (e.entity_id = '4354') OR (e.entity_id = '4353') OR (e.entity_id = '4352') OR (e.entity_id = '4351') OR (e.entity_id = '4350') OR (e.entity_id = '4349') OR (e.entity_id = '4348') OR (e.entity_id = '5203'))) ORDER BY find_in_set(e.entity_id,'8534,4184,4185,4183,13314,5069,11217,4355,4354,4353,4352,4351,4350,4349,4348,5203') ASC, cat_index_position ASC, e.entity_id ASC LIMIT 16

Answer 1

I had a similar issue with a module and the way I fixed it was setting the visibility as below. Just add the rest of your collection parameters after "setVisibility".

$products = Mage::getResourceModel('catalog/product_collection')
    ->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds())

Answer 2

What you can do to remove that statement automatically added is manipulate the select object directly.

So first write your first part of code:

$collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('*')
        ->addAttributeToFilter('entity_id', $productIds)
        ->addOrder('entity_id');

This code should not trigger any order.

Now you can get the order part of the select object and remove the part you don't want:

// Get the order part
$order = $collection->getSelect()->getPart('order');

// Loop through the order parts
foreach ($order as $key => $value) {
    // Remove if the unwanted key is found
    if ($key == "cat_index_position") {
        unset($order[$key]);
    }
}

// Assign the new order part
$collection->getSelect()->setPart('order', $order);

Once you're removed your unwanted order statement you can write the rest of your code:

$sortIds = array_map('intval', $productIds);
$collection->getSelect()
       ->order("find_in_set(e.entity_id,'".implode(',', $sortIds)."')");

NB: that is the general idea you may have to modify the foreach loop to match your needs.

Answer 3

There's an extension to fix/work around this issue: https://github.com/Webtise/cat-index-position-fix