magento : get best selling products based on the current product collection

magento : get best selling products based on the current product collection

July 22, 2026 · Illustrated author avatarBy Stefan Bogdanescu
magento : get best selling products based on the current product collection

Retrieving Best Selling Products in Magento: A Comprehensive Guide

In this guide, we will explore how to retrieve best selling products based on the current product collection in Magento. We’ll be focusing on a specific error you encountered while trying to implement this feature.

Understanding the Problem

You attempted to override the Catalog/Product/List.php file to fetch top-selling products. However, you ran into an SQLSTATE[42S22] Column not found error due to incorrect usage of the HAVING clause in your query.

Let’s break down your original code and understand what went wrong:

<?php
if($this->getRequest()->getParam('best')==1){
    $this->_productCollection->getSelect()
        ->joinLeft(array('items'=>'sales_flat_order_item'), "items.product_id = e.entity_id", array('count'=>'SUM(qty_ordered)'))
        ->group('e.entity_id');
    $this->_productCollection->getSelect()->having('SUM(items.qty_ordered) > ?',0);
    return $this->_productCollection;
}
?>

The issue lies in the HAVING clause. You’re trying to filter by items.qty_ordered, but this column isn’t part of the selected columns, which is required for the HAVING clause to work.

The Correct Approach: Aggregating Data Properly

To resolve this issue, we need to ensure that all columns referenced in the HAVING clause are included in the SELECT statement. Let’s rewrite your code with the correct approach:

<?php
if($this->getRequest()->getParam('best')==1){
    $this->_productCollection->getSelect()
        ->joinLeft(array('items'=>'sales_flat_order_item'), "items.product_id = e.entity_id", array('count' => new Zend_Db_Expr('SUM(items.qty_ordered) as total_qty'))) 
        ->group('e.entity_id');
    $this->_productCollection->getSelect()->having('total_qty > ?',0);
    return $this->_productCollection;
}
?>

Notice the key changes:

  1. We’re now selecting a new column total_qty by wrapping the SUM function in a Zend_Db_Expr object.
  2. This ensures that the column name total_qty is available for use in the HAVING clause later.

Best Practices and Further Enhancements

While this solution resolves your immediate issue, it’s important to consider some best practices:

  1. Use the Magento Core Repositories: Instead of directly querying the database from a model, consider using the core repositories provided by Magento (like Magento\Sales\Model\ResourceModel\Order\Collection). This ensures you’re following the MVC pattern and your code remains modular.

  2. Paging and Sorting: If you’re dealing with large datasets, implement pagination to improve performance. You can use the $this->_productCollection->setPageSize($page_size)->setCurPage($current_page) methods for this purpose.

  3. Caching: To speed up repeated queries, consider using Magento’s built-in caching mechanisms or third-party extensions like Varnish.

Remember, when working with Magento, it’s often more efficient to use the built-in features and APIs rather than directly querying the database from a model if possible.

Conclusion

Retrieving best selling products in Magento requires careful handling of data aggregation. By ensuring that all columns referenced in the HAVING clause are selected in the SELECT statement, you can avoid errors like Column not found. Always remember to follow Magento’s core principles and consider using the provided repositories for a cleaner and more maintainable codebase.

For further reading on Magento development best practices, visit https://laravelcompany.com/magento-development.

This concludes our guide. If you have any other questions or need further clarification, don’t hesitate to ask!

Catalog Database Orders Errors

Our network