2

For Magento 1.x

How do I sort product based on a date attribute by a month and year only, rather than on the whole date? The code looks a bit like this:

Mage::getSingleton('...')->getProductCollection()
->addAttributeToSort('published_date', 'DESC') // -> want to sort on Month and Year
->addAttributeToSort('price', 'DESC');

The overall requirement is to sort products based on Year, then Month, then Price... hence this question.

Thanks in advance!

0

What I would do is get the collection first. Rework on the published_date format to get only the months and years. Make an array and sort the array by this field. There is surely another better solution though. Hope it helps a bit.

0

SQL provides functions for getting only the MONTH or YEAR out of an date. So f.e. the sql statement would solve this https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html:

SELECT * FROM catalog_product_entity 
order by 
YEAR(created_at) desc,
MONTH(created_at) desc,
sku desc;

I replaced the price by the the sku in this example; and used the created_at date, so i only needed to select on a single table, instead of doing some additional join-stuff.

I don't think you are able to use SQL functions inside Magentos addAttributeToSort method. Depending on your case maybe a solution could be to filter the results in the code afterwards. Of course this will take a lot more time, than sorting directly with SQL.

*edit:
Well, you could do it by using the following, but this is really not the best code style:
...->getSelect()->order(...);

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.