Join product_collection with order_item_collection

by Martin Secher Skeem   Last Updated January 13, 2018 10:09 AM

I am trying to list the most recently ordered products for a custom module (Magento 1.9.3.7).

I have it working, but I believe that the code is less efficient than it could be + the ordering of the products are not by the date of last purchase, which would be ideal.

I imagine that I could just join the products collection onto the 6 most recently sold items, but can't quite make this work. Is there a way to achieve this in one query?

$itemsCollection = Mage::getResourceModel('sales/order_item_collection');

$itemsCollection
->getSelect()
->limit(6)
->reset(Zend_Db_Select::COLUMNS)
->columns('product_id')
->group('product_id')
->join('catalog/product','`main_table`.`product_id` = `catalog/product`.`entity_id`')
->order('MAX(`main_table`.created_at) DESC');

$productids = [];
foreach($itemsCollection as $item) {
    $productids[] = $item->getProductId();
}

$productsCollection = Mage::getResourceModel('catalog/product_collection')
->addIdFilter($productids)
->addAttributeToSelect('*')
->addUrlRewrite()
->addMinimalPrice()
->addFinalPrice();

Br, Martin



Related Questions





Order shows no line items for Bundled Product

Updated June 02, 2015 23:04 PM