Magento – product collection sorting by price is not working

categorymagento-1.9pricesortsorting

I want to sort the product collection by price. For this i am using

->setOrder('price', 'DESC') or ->addAttributeToSort('price', 'DESC') is not working but it is working with ->addAttributeToSort('name','DESC') perfectly.

$_productCollection = Mage::getModel('catalog/product')
    ->getCollection()
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('status', 1)
    ->addAttributeToSort('price', 'ASC')
    ->addAttributeToFilter('visibility', 4)
    ->addAttributeToFilter('category_id', array('in' => $categoryIds));

Best Answer

Try this add price to collection:

$_productCollection->joinAttribute(
            'price',
            'catalog_product/price',
            'entity_id',
            null,
            'left',
            Mage::app()->getStore()->getId()
        );

Or

$_productCollection
            ->addMinimalPrice()
            ->addFinalPrice()
            ->addTaxPercents()

Some Additional info:

setOrder() does not works because of

Magent use Eav Structure for product attributes Whenever magento add an attribute to it product Collection then it join the collection with product attribute respective table (example catalog_product_varchar,catalog_product_ etc.)

And give an alias name to that attribute table and this attribute name in collection.

As you have using setOrder('price', 'DESC') that it trying field to main table catalog_product_entity which does not exit that why it is not works.

But whenever you use addAttributeToSort() function magento build automatically logic which properly call the field

  if (isset($this->_staticFields[$attribute])) {
            $this->getSelect()->order("e.{$attribute} {$dir}");
            return $this;
        }
        if (isset($this->_joinAttributes[$attribute])) {
            $attrInstance = $this->_joinAttributes[$attribute]['attribute'];
            $entityField = $this->_getAttributeTableAlias($attribute) . '.' . $attrInstance->getAttributeCode();
        } else {
            $attrInstance = $this->getEntity()->getAttribute($attribute);
            $entityField = 'e.' . $attribute;
        }

        if ($attrInstance) {
            if ($attrInstance->getBackend()->isStatic()) {
                $orderExpr = $entityField;
            } else {
                $this->_addAttributeJoin($attribute, 'left');
                if (isset($this->_joinAttributes[$attribute])||isset($this->_joinFields[$attribute])) {
                    $orderExpr = $attribute;
                } else {
                    $orderExpr = $this->_getAttributeTableAlias($attribute).'.value';
                }
            }

You can the fundamental logic of addAttributeToSort() on class Mage_Eav_Model_Entity_Collection_Abstract

That reason magento use AddAttributeToSort() to filter the product collection

Related Topic