How to retrieve last purchased products on a store/website level

Question

What is the most optimized why to retrieve last purchased products on a store/website level in order to use them on template block?

Answer 1

Probably the best way to go about this is to use the sales/order_item collection and join it with the order table to filter by store ID.

The following collection would retrieve the last 5 items from orders placed on store 1

$store_id = 1;

$collection = Mage::getModel('sales/order_item')
            ->getCollection()
            ->addAttributeToSelect('product_id')
            ->addAttributeToSelect('created_at')
            ->addAttributeToSort('main_table.created_at', 'DESC');

$collection->getSelect()->join( array('so' => 'sales_flat_order'), 'main_table.order_id = so.entity_id AND so.store_id=' . $store_id, array(''));
$collection->getSelect()->limit(5);