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

  • I think you need to add addAttributeToSelect('cat_index_position'), I believe it is obtained by a JOIN when building the collection and is not retrieved automatically. – Julien Lachal May 31 '16 at 12:20
  • This does not work, and shouldn't be needed since I have addAttributeToSelect('*'). Looking at the SQL statement after adding addAttributeToSelect('cat_index_position') doesn't add any extra JOINs. – Vindfrisk May 31 '16 at 12:30
  • Alright, do you mind updating the question with the whole SQL Query pls (just echo (string)$collection->getSelect() )? – Julien Lachal May 31 '16 at 13:02
  • @JulienLachal yes, I've added that information to the main question. – Vindfrisk May 31 '16 at 13:37
  • Check if there is any observer hooking into product collection before load adding this order. This is definitely not standard behaviour. – Zefiryn Jun 18 '16 at 14:25

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())

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.

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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