I am kinda new in Magento and I have been searching for the query over the internet for the passed few days regarding the "Least selling products" in Magento or a list of products that are not sold or at least have the fewest quantity sold and i have not been that lucky enough to find one.

My first attempt was just to reverse the query from best selling products (sales_flat_order_item) but the problem is that I would only be getting a list products that are sold with the least quantity not the products which were not sold or 0 quantity sold.

Can anyone at least give me some an idea or tips on how to start this one?

Thank you in advance.


2 Answers 2

$catalog = Mage::getModel('catalog/product')->getCollection();
$unsold = array();
foreach ($catalog as $product) {
    $sales = Mage::getModel('sales/order_item')->getCollection();
    $sales->addFieldToFilter('product_id', array('eq' => $product->getId());
    if (!$sales->getSize()) {
       $unsold[] = $product->getId();

// Product ids of products that haven't been sold

I think this should work! Untested, so give it a shot. You can filter the product collection how you like (disabled, enabled, etc). There are probably more efficient ways to do this, but if you're just running a report every so often, it shouldn't be a big deal.

  • hi i just have another question regarding your answer, what is this !$sales->getSize() referring to? and what if Im wanted to translate that line to a simple query? @pspahn
    – user20539
    Jan 21, 2015 at 8:13
  • getSize() basically checks the count on a Magento collection. As we loop through each product, we check the sales/order_item collection for entries corresponding to each product ID. If none are found (ie. !$sales->getSize()) then we store that product ID in an array we can use later. I am not sure what you mean by "translate that line to a simple query".
    – pspahn
    Jan 21, 2015 at 8:33
  • Now I get it. What i was trying to say earlier ("translate that line to a simple query") is I was converting your answer to a Query and here is it what I got: select * from catalog_product_entity A where ( select count(*) from sales_flat_order_item B where A.entity_id = B.product_id) But I was having a different result with your provided solution: My query result is: 3 4 10 12 19 And the result from your solution was: [0] => 6 [1] => 7 [2] => 8 [3] => 9 [4] => 11 [5] => 13 [6] => 17 [7] => 18 [8] => 20 [9] => 21 different result different ids @pspahn
    – user20539
    Jan 21, 2015 at 8:45
  • can you please confirm whether my query is correct? Thank you very much @pspahn
    – user20539
    Jan 21, 2015 at 8:49
  • this has already been resolved. THANKS FOR YOUR HELP! :-)
    – user20539
    Jan 21, 2015 at 9:02

another approach is to hang this off the sales reports, bearing in mind that your statistics for the reports need to be up to date.

        $storeId = Mage::app()->getStore()->getId();
        $daysBack = 1;
        $customCollection = Mage::getResourceModel('reports/product_collection')
                        $this->getDateBack($daysBack), $this->getTodayDate()
                    'ordered_qty ' . Zend_Db_Select::SQL_ASC
                    'order_items_name ' . Zend_Db_Select::SQL_ASC

And the two methods used for days calculation:

protected function getDateBack($value)
        return $dateBack = date(
            'Y-m-j G:i:s',
            strtotime('-' . $value . ' day' . $this->getTodayDate())

    protected function getTodayDate()
        return Mage::app()->getLocale()->date()->toString(

So, you easily flip that for best selling ;)

                        'ordered_qty ' . Zend_Db_Select::SQL_DESC

or go for least/best viewed products, if you want, by simply chnaging what gets ordered

                    'views ' . Zend_Db_Select::SQL_ASC
                    'entity_id ' . Zend_Db_Select::SQL_DESC

Note: this code was extracted from my Dynamic Category Products Extension, so untested standalone as given, but it will give the idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.