1

I have a page with promotions based on set special price from/to date attribute and it shos fine. I have created a new attribute called flash that is a yes/no value. I want to order the previous collection so I show first the ones that have such attribute as a Yes. This is my code and it doesn't sort, the sql query doesn't have a order by clause. It doesn't work replacing setOrder with addAttributeToSort either.

    $collection = Mage::getResourceModel('catalog/product_collection');
    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

    $collection = $this->_addProductAttributesAndPrices($collection)
        ->addStoreFilter()
        ->addAttributeToFilter('special_from_date', array('date' => true, 'to' => $todayDate))
        ->addAttributeToFilter('special_to_date', array('or'=> array(
            0 => array('date' => true, 'from' => $todayDate),
            1 => array('is' => new Zend_Db_Expr('null')))
        ), 'left')
        ->setOrder('flash', 'ASC')
        ->setPageSize($this->getProductsCount())
        ->setCurPage(1)

    ;
2

Have a look on the query to fix this issue.

die((string)$collection->getSelect());

and check whether the ORDER BY clause is there. The code is correct from my perspective.

Works

   $collection = Mage::getResourceModel('catalog/product_collection');
    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

    $collection->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addUrlRewrite()
        ->addStoreFilter()
        ->addAttributeToFilter('special_from_date', array('date' => true, 'to' => now()))
        ->addAttributeToFilter('special_to_date', array('or'=> array(
                0 => array('date' => true, 'from' => now()),
                1 => array('is' => new Zend_Db_Expr('null')))
                                                  ), 'left')
        ->addAttributeToSort('name')
        ->setPageSize(10)
        ->setCurPage(1)

    ;
    die((string)$collection->getSelect());

I have a ORDER BY clause. Did you refresh indexes and caches? I don't think this is the problem, but might be. I have no attribute flash here, so I just tried with name and this works.

  • My code does not have an order by clause (as I said on my question) – Elaine Marley May 9 '13 at 7:33
  • Sorry, I overread this part :-/ This doesn't make sense. I see no possibility neither in setOrder nor in addAttributeToSort to have NO ORDER BY clause. Edited the post. – Fabian Blechschmidt May 9 '13 at 8:04
  • You are right, when I have ->addAttributeToSort('name') I see the order by, if I change it to ->addAttributeToSort('flash') it's gone! – Elaine Marley May 9 '13 at 8:19
  • Make sure the attribute you are trying to sort by is in the addAttributeToSelect() If that does not work please make sure that you have set the property 'use in product listing' to "Yes" is of the flash attribute. If that doesn't work, well it just has to work :-P – Kenny May 9 '13 at 13:45
  • there is no need for this. AddAttributeToSort handles the joins for itself. – Fabian Blechschmidt May 9 '13 at 14:03
0

You could try the following code (Just to make sure that all previous order by's are removed):

$collection = Mage::getResourceModel('catalog/product_collection');
Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

$collection = $this->_addProductAttributesAndPrices($collection)
    ->addStoreFilter()
    ->addAttributeToFilter('special_from_date', array('date' => true, 'to' => $todayDate))
    ->addAttributeToFilter('special_to_date', array('or'=> array(0 => array('date' => true, 'from' => $todayDate),1 => array('is' => new Zend_Db_Expr('null')))), 'left')
    ->setPageSize($this->getProductsCount())
    ->setCurPage(1)
;

$collection->clear()->getSelect()->reset('order');
$collection->getSelect()->order('flash ASC');

Let me know whether this works ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.