1 Comment

addAttributeToSort with MySQL “IN”

ChangeLog (_cl) table implementation for indexing in Magento2
Problem with Customer IDs after export

I want to sort a catalog_product_collection by multiple attributes but with special input.

As I understand the method addAttributeToSort calling it like this

addAttributeToSort("attribute");

Seems to do the trick, if it can load the attribute. But I need to call it like that:

addAttributeToSort("attribute IN ('001')");

Which will fail because it cannot load an attribute called attribute IN ('001')

How would I sort the collection in this case?

Reason: We want the product collection order in a special format. I have the algorithm done and its working fine (pagionation is broken now, but thats another topic) but a co worker showed me that you could so an ORDER BY like that:

ORDER BY x.Component_Stone_Color IN ('001') DESC, x.Component_Stone_Color IN ('280') DESC, ....

Edit

Something like this seems to work:

$sql = $collection->getSelect();
$sql->order(array("sw_stone_color IN ('001') DESC", "sw_stone_color IN ('280') DESC", "sw_...", ...));

But the needed attributes are not (yet?) selected.
An $collection->addAttributeToSelect(array('sw_stone_color', 'sw_effects', 'sw_foiling', 'sw_motifs')); doesnt seem to do the trick, sadly. (They are simply not selected when I look at the output query)

I’m in the catalog_product_collection_load_before event.

Related Posts

  • Magento product save is not working when i add special price in it (2)
    I am working in Magento Enterprise. When I save a product without any special price, everything is working fine and products are saving from admin. But when I add a special price, product save is not working. My page does not stop loading when I click save. It is not showing any […]
  • Invalid use of where clause with UNION – Magento (0)
    I am trying to merge two collections in magento, but I am getting this error Invalid use of where clause with UNION Zend_Db_Select->_where('`id` IN(0)', '', NULL, true) Also, When I print the collection query and the query is working fine in mysql workbench, but when I pass […]
  • Invalid use of where clause with UNION – Magento (0)
    I am trying to merge two collections in magento, but I am getting this error Invalid use of where clause with UNION Zend_Db_Select->_where('`id` IN(0)', '', NULL, true) Also, When I print the collection query and the query is working fine in mysql workbench, but when I pass the […]
  • Invalid use of where clause with UNION – Magento (0)
    I am trying to merge two collections in magento, but I am getting this error Invalid use of where clause with UNION Zend_Db_Select->_where('`id` IN(0)', '', NULL, true) Also, When I print the collection query and the query is working fine in mysql workbench, but when I pass […]
  • Invalid use of where clause with UNION – Magento (0)
    I am trying to merge two collections in magento, but I am getting this error Invalid use of where clause with UNION Zend_Db_Select->_where('`id` IN(0)', '', NULL, true) Also, When I print the collection query and the query is working fine in mysql workbench, but when I pass the […]
  • Layered Navigation with custom collection (0)
    I have created a category but I am using a custom layout which overrides the default product collection with the following code: $searchQuery = $this->getLayer()->getCurrentCategory()->getName(); $_product = Mage::getModel('catalog/product'); $attr = […]
  • Layered Navigation with custom collection (0)
    I have created a category but I am using a custom layout which overrides the default product collection with the following code: $searchQuery = $this->getLayer()->getCurrentCategory()->getName(); $_product = Mage::getModel('catalog/product'); $attr = […]
  • Layered Navigation with custom collection (0)
    I have created a category but I am using a custom layout which overrides the default product collection with the following code: $searchQuery = $this->getLayer()->getCurrentCategory()->getName(); $_product = Mage::getModel('catalog/product'); $attr = […]
  • Count getSingleton Product Collection Without changing it? (5)
    How can i count an productcollection without changing it? when i do: $test = Mage::getSingleton('catalog/layer')->getProductCollection()->addFieldToFilter('special_price', 150); And then call count($test) it replaces my productcollection. EDIT: Basicly i want […]
  • Count getSingleton Product Collection Without changing it? (5)
    How can i count an productcollection without changing it? when i do: $test = Mage::getSingleton('catalog/layer')->getProductCollection()->addFieldToFilter('special_price', 150); And then call count($test) it replaces my productcollection. EDIT: Basicly i want […]
ChangeLog (_cl) table implementation for indexing in Magento2
Problem with Customer IDs after export

1 Comment on this article

  • User November 12, 2016

    You have to use Zend_Db_Expr while using something different from a simple field name:

    $collection->getSelect()->order(array(new Zend_Db_Expr('sw_stone_color IN ('001') DESC'), new Zend_Db_Expr(...)));
    

    Reply

Add a comment