How do i sort magento products collection with 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 the product collection by this value in a predefined order e.g. "Frühjahr/Sommer", "Herbst/Winter", "" OR "Herbst/Winter", "Frühjahr/Sommer", ""

This is alternating. The empty values should be allways at the end but maybe there are more values to come so it must be a fixed predefined order.

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

The problem is i have no clue how to execute this command in Magento. I know the "->setOrder"-Method but i need to commit a fixed order instead of using DESC or ASC.

I search a lot on stackoverflow and google but no answer so far.

I hope you can help me

Thanks

[edit in responds to the answer of magalter]

unfortunately it doesn't work i tried it before:

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

The result is "There has been an error 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 get the following error message "Unknown column 'season' in 'order clause'"

If I order by "season" DESC

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

it works ... and 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 is order by DESC not by a predefined order like i want it so i can't use it.

What i need is the SQL Statement above with

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

instead

ORDER BY `season` DESC

Another idea ???

Answer 1

Try to use Zend_DB_Select order rather then Magento setOrder method

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

Example:

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

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