Magento Order product collections by best seller / Rating

Question

On magento product view page, i'm trying to order upsell products by best selling / more rating

On a search i found that this code will work to get order product collection by best seling,

$storeId    = Mage::app()->getStore()->getId();

$products = Mage::getResourceModel('reports/product_collection')
            ->addOrderedQty()
            ->addAttributeToSelect(array('name', 'price', 'small_image')) //edit to suit tastes
            ->setStoreId($storeId)
            ->addStoreFilter($storeId)
            ->setOrder('ordered_qty', 'desc'); //best sellers on top

Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($products);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($products);

print_r($products->getData());

I have two issues where to add this code & how about adding set order by rating too ?

Any help is much appreciated.

Answer 1

You shouldn't implement your ordering this way. The problem is, that this counts all the order_items. No index on this and with the time they increase a lot in count, therefore you can't check what the performance will be in a year or two.

I suggest to create a new attribute on the product, a static one, put an index on it and order by this. Then you can implement (an) observer(s) who updates this attribute (however you want to order it), after someone added a rating or a order is placed.