How to sort a magento product collection using ORDER BY FIELD

Question

I have a custom product attribute with actually three possible values: "Frühjahr / Sommer", "Herbst / Winter", "" (empty).

I want to sort a collection of products by this value in a predefined order, for example, "Frühjahr / Sommer", "Herbst / Winter", "OR" Herbst / Winter "," Frühjahr / Sommer ","

It alternates. Empty values ​​should always be at the end, but perhaps there are more values, so it should be fixed in a predefined order.

I need to execute the following MySQL FIELD ORDER (saison, "Frühjahr / Sommer", "Herbst / Winter", "")

The problem is that I do not know how to execute this command in Magento. I know the "-> setOrder" method, but I need to fix a fixed order instead of using DESC or ASC.

I am looking a lot in stackoverflow and google, but there is no answer yet.

I hope you help me.

thanks

[edit in response to magalter's answer]

Unfortunately, it does not work, I tried this before:

$this->_collection->getSelect()->order(new Zend_Db_Expr("FIELD(season, 'Frühjahr/Sommer','Herbst/Winter','')")); 

Result: "An error occurred while processing your request.

 $this->_collection->getSelect(); 

Return:

 SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='124' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 ORDER BY FIELD(season, 'Frühjahr/Sommer','Herbst/Winter','') 

I executed this sql statement in phpMyAdmin and received the following error message "Unknown column" in the order order section

If I order a "season" DESC

 $this->_collection->setOrder('season', 'DESC'); 

it works ... and will generate the following sql statement

 SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IF(season_option_value_t2.value_id IS NULL, season_option_value_t1.value, season_option_value_t2.value) AS `season` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='124' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOIN `catalog_product_entity_int` AS `season_t1` ON e.entity_id=season_t1.entity_id AND season_t1.attribute_id='180' AND season_t1.store_id=0 LEFT JOIN `catalog_product_entity_int` AS `season_t2` ON e.entity_id=season_t2.entity_id AND season_t2.attribute_id='180' AND season_t2.store_id='1' LEFT JOIN `eav_attribute_option_value` AS `season_option_value_t1` ON season_option_value_t1.option_id=IF(season_t2.value_id > 0, season_t2.value, season_t1.value) AND season_option_value_t1.store_id=0 LEFT JOIN `eav_attribute_option_value` AS `season_option_value_t2` ON season_option_value_t2.option_id=IF(season_t2.value_id > 0, season_t2.value, season_t1.value) AND season_option_value_t2.store_id=1 ORDER BY `season` DESC 

But this DESC order is not in the predefined order as I want it, so I cannot use it.

What I need is the SQL expression above with

 ORDER BY FIELD(season, 'Frühjahr/Sommer','Herbst/Winter','') 

instead

 ORDER BY `season` DESC 

Another idea ???

Answer 1

Finally, I found a solution. Maybe another user needs this, here it is:

(This may not be the best solution, but it works)

I created a new Modul and expanded Mage_Catalog_Block_Product_List_Toolbar. Then I redefine "setCollection" -Method, like this

 public function setCollection($collection) { $this->_collection = $collection; $this->_collection->setCurPage($this->getCurrentPage()); // we need to set pagination only if passed value integer and more that 0 $limit = (int)$this->getLimit(); if ($limit) { $this->_collection->setPageSize($limit); } if ($this->getCurrentOrder()) { $mySource = $this->_collection->getEntity()->getAttribute('season'); $mySource = $this->_collection->getEntity()->getAttribute('season')->getSource(); //Mage_Eav_Model_Entity_Attribute_Source_Table $collection = $this->_collection; $valueTable1 = 'season_t1'; $valueTable2 = 'season_t2'; $collection->getSelect() ->joinLeft( array($valueTable1 => $mySource->getAttribute()->getBackend()->getTable()), "e.entity_id={$valueTable1}.entity_id" . " AND {$valueTable1}.attribute_id='{$mySource->getAttribute()->getId()}'" . " AND {$valueTable1}.store_id=0", array() ) ->joinLeft( array($valueTable2 => $mySource->getAttribute()->getBackend()->getTable()), "e.entity_id={$valueTable2}.entity_id" . " AND {$valueTable2}.attribute_id='{$mySource->getAttribute()->getId()}'" . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'", array() ); $valueExpr = $collection->getSelect()->getAdapter() ->getCheckSql("{$valueTable2}.value_id > 0", "{$valueTable2}.value", "{$valueTable1}.value"); Mage::getResourceModel('eav/entity_attribute_option') ->addOptionValueToCollection($collection, $mySource->getAttribute(), $valueExpr); $sortBySeason = "FIELD(IF(season_option_value_t2.value_id IS NULL, season_option_value_t1.value, season_option_value_t2.value), "; $getSelectedSeason = Mage::getStoreConfig('sortproductsbyseason_options/general/current_season'); if($getSelectedSeason == "herbst_winter"){ $sortBySeason .= "'Frühjahr/Sommer', 'Herbst/Winter' ,''"; }else{ $sortBySeason .= "'Herbst/Winter', 'Frühjahr/Sommer' ,''"; } $sortBySeason .= ") DESC"; $this->_collection->getSelect()->order(new Zend_Db_Expr($sortBySeason)); $this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection()); } return $this; } 

Answer 2

Try using Zend_DB_Select rather than the Magento setOrder method

 $colection->getSelect()->order(..) 

Example:

 ->order(array('line_items_per_product DESC', 'product_id')); 

See http://framework.zend.com/manual/1.12/en/zend.db.select.html#zend.db.select.building.order for more examples

Answer 3

How about this?

 $collection = Mage::getModel('catalog/category')->load($categoryId) ->getProductCollection() ->addAttributeToSort('name', 'ASC');