doing topsellers/bestsellers by category elegantly

Question

So I've been tasked with making a bestsellers box, yet I've heard a horror story about the bestsellers box in a previous site taking down the site because of the extensive load.

First, I should mention that this site has quite a bit of products, somewhere in the thousands or ten thousands.

Ive seen extensions and code snippets that do bestsellers, and it seems to make use of something like this:

$collection = Mage::getResourceModel('reports/product_sold_collection')
                            ->addOrderedQty()
                            ->setStoreId($storeId)
                            ->addStoreFilter($storeId)
            ->setDateRange($sellDate['startdate'],          $sellDate['todaydate']) //
                            ->addUrlRewrite()
                            ->setOrder('ordered_qty', 'desc')
                            ->setPageSize($this->getModel()->getHomepageProductsLimit())
                            ->setCurPage(1)
            ->setOrder('ordered_qty', 'desc');

I looked into that and it seems like whats going on is that its going through all the products, and getting ordered_qty from those products.

I also noticed that there were some cool tables called sales_bestsellers_aggregated_monthly / sales_bestsellers_aggregated_daily / sales_bestsellers_aggregated_weekly that get populated by a cron task.

I thought it would be cool to make use of these tables, but the problem with that is A) there is no category data and B) its monthly/daily/weekly rather than all time. That latter may not be such an issue, but its something to be considered.

I guess what I am looking for is some thoughts on how to approach this in a way where its not going to take down the site. The site will be behind varnish, but I heard even behind varnish, the last site was taken down by bestsellers.

Thanks!

Answer 1

The collection shown is actually the Magento-way of doing this. (Sorting once is enough, of course ;) ).

It does not run against all products, it is Magento's way to implement a database query using collections. The "Magento-way" doing the join you want would be the same (pseudo-code):

$collection = Mage::getModel('catalog/product')->getCollection()->join('catalog_category_product as cpp1')->where(...);

You can see the executed SQL-statement:

echo $collection->getSelect();

Ask if u need more help.

Answer 2

This will give you something to work with and pulls the top 50 best sellers. I have noticed that daily, monthly, yearly actually don't calculate properly in Magento/DB - not sure which one and they are "ALL TIME" which can be problematic. Anyway I know this is a late reply but for future reference for people who need to query best sellers, this should help. You can replace product_name etc. with * if you like. I just needed the following rows.

SELECT product_name, entity_id, product_id, sku, url_path, small_image, SUM( qty_ordered ) AS total
    FROM sales_bestsellers_aggregated_monthly, catalog_product_flat_1
    WHERE store_id =0
    AND entity_id = product_id
    GROUP BY product_name
    ORDER BY total DESC 
    LIMIT 50