Sorting on a date's component

Question

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!

Answer 1

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.

Answer 2

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(...);