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)

    ;
Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
5
  • My code does not have an order by clause (as I said on my question)
    – Elaine Marley
    Commented May 9, 2013 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
    Commented May 9, 2013 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
    Commented May 9, 2013 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
    Commented May 9, 2013 at 13:45
  • there is no need for this. AddAttributeToSort handles the joins for itself.
    – Fabian Blechschmidt
    Commented May 9, 2013 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 ;)

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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