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