We have a category called "Clearance" on our Magento website. Ideally it would be nice to be able to get a report of all orders sold in that category.
Is there anyway I can join the orders collection with the products collection and filter by category id?
Here is a similar thread: http://www.magentocommerce.com/boards/viewthread/44296/
Any ideas, or am I crazy? It doesn't need to be pretty.
All order data in Magento is product-specific. It doesn't store anything about what category the products are in. So, as you have found out, there are no reports available in Magento for that sort of thing. It makes it challenging as well because of the fact that products can be in multiple categories. Let's say you have a product that is in 'Clearance' and another category. How can you know which category the product was purchased from? (You can't).
A dirty way to do it would be to sift through all of the orders, and on each product, check to see if it belongs to the 'Clearance' category, as you mentioned with the Order Collection.
Unfortunately, this gets complicated rather quickly, and the effort may really not be worth the time.
I'm building a system that overrides the Mage_Checkout_OnepageController's function saveOrderAction may get me what I need.