How do i sort magento products collection with ORDER BY FIELD

How do i sort magento products collection with ORDER BY FIELD

July 8, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How do i sort magento products collection with ORDER BY FIELD

Sorting Magento Product Collection by Custom Attribute Value

In Magento, sorting products based on a custom attribute value isn’t straightforward due to the use of EAV (Entity-Attribute-Value) architecture. However, it is possible with the help of MySQL’s FIELD function and Magento’s collection manipulation methods.

The Problem: Sorting by a Custom Attribute

You have a custom product attribute named “saison” with three possible values: “Frühjahr/Sommer”, “Herbst/Winter”, and an empty string. You want to sort the product collection in a predefined order, such as:

  1. “Frühjahr/Sommer”
  2. “Herbst/Winter”
  3. (empty)

You know that you can use the FIELD function in MySQL like this:

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

However, executing this command directly in Magento isn’t as simple as it seems.

The Solution: Manipulating the Collection Select

Magento uses Zend Framework’s Zend_Db_Select to generate SQL statements for collections. To insert the FIELD function into your collection’s select statement, you need to manipulate the select object.

Here’s a step-by-step guide:

  1. Access the collection’s select object in your controller or helper:
$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('saison')
    ->addFieldToFilter('saison', array('neq' => '')); // Exclude empty values

$select = $collection->getSelect();
  1. Use Zend_Db_Expr to create an expression for the FIELD function:
$expr = new Zend_Db_Expr("FIELD(e.saison, 'Frühjahr/Sommer', 'Herbst/Winter', '')");
  1. Add this expression as a condition in your select object:
$select->order($expr);

The complete code looks like this:

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('saison')
    ->addFieldToFilter('saison', array('neq' => ''));

$expr = new Zend_Db_Expr("FIELD(e.saison, 'Frühjahr/Sommer', 'Herbst/Winter', '')");
$select = $collection->getSelect();

$select->order($expr);

The Result

This code will generate an SQL statement that sorts your product collection based on the “saison” attribute using the FIELD function. The resulting products should be in the order you specified.

Remember to clear your cache after making changes to collections, as Magento often caches collection queries for performance reasons.

Conclusion

Manipulating collections and sorting them by custom attributes in Magento requires a combination of knowledge about MySQL functions and Magento’s architectural components. By using Zend_Db_Expr and manipulating the select object directly, you can achieve complex sorting requirements that aren’t possible with the default Magento methods.

If you found this solution helpful, consider exploring other advanced features on Laravel Company, where we provide in-depth tutorials and guides for Magento development.

Catalog Database Orders Errors

Our network