magento how to list most viewed product with the minimal price

magento how to list most viewed product with the minimal price

August 9, 2026 · Illustrated author avatarBy Stefan Bogdanescu
magento how to list most viewed product with the minimal price

Combining Most Viewed Products with Minimal Price in Magento

As a senior developer, I understand your challenge of combining multiple SQL queries to list the most viewed products with the minimal price in Magento. Let’s break down this complex task step by step.

Understanding Your Current Approach

You’re on the right track by using the getResourceModel('reports/product_collection') method for getting the most viewed products and the native product collection methods for adding selectable attributes, filters, and prices. However, you’ve encountered difficulties when trying to combine these two parts.

Challenges with Current Implementation

The issue lies in the way Magento’s report collections are structured. The addViewsCount() method is not just adding a simple COUNT() column but is fundamentally changing the core of the SQL query by joining multiple tables and aggregating data based on views count. When you try to add additional methods like addMinimalPrice() or addFinalPrice(), Magento’s framework overrides these changes, leading to inconsistencies in your desired result.

A More Advanced Approach

Instead of relying solely on built-in collection methods, we need to dive deeper into the Magento database structure and execute a custom SQL query that combines both views count and pricing information.

First, let me clarify that the attribute set id filter is already incorporated in the WHERE clause of your sample SQL queries. So, you don’t need to add an additional addAttributeToFilter('attribute_set_id', 4) call in the collection methods.

Executing a Custom SQL Query

Magento provides a mechanism called “Resource Model” that allows us to execute custom database queries without directly interacting with the PDO or MySQLi extensions. Here’s how you can achieve this:

$resource = Mage::getSingleton('core/resource');

$query = "
SELECT COUNT(report_table_views.event_id) AS `views`, e.*, price_index.price, price_index.tax_class_id,
price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price,
price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price,
price_index.max_price, price_index.tier_price
FROM report_event AS report_table_views
INNER JOIN catalog_product_entity AS e ON e.entity_id = report_table_views.object_id AND e.entity_type_id = 4
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id =
1 AND price_index.customer_group_id = 0
WHERE (report_table_views.event_type_id = 1 AND `e`.`attribute_set_id` = '4')
GROUP BY e.entity_id HAVING COUNT(report_table_views.event_id) > 0 ORDER BY views DESC";

// Execute the query
$connection = $resource->getConnection('core_read');
$result = $connection->query($query);

Processing the Result Set

The $result variable will hold an instance of Varien_Db_Statement_Pdo_Mysql. You can use the fetchAll() method to retrieve the result set as an array:

$products = $result->fetchAll();

foreach ($products as $product) {
    // Process each product, e.g., display views count and minimal price
    echo "Product ID: {$product['entity_id']}, Views Count: {$product['views']}, Minimal Price: {$product['minimal_price']}";
}

Conclusion

Combining the most viewed products with minimal price in Magento requires a deep understanding of its database schema and resource models. By executing a custom SQL query using Magento’s core resource, we can achieve your desired result.

Remember to always test queries thoroughly on development or staging environments before deploying them to production sites. Additionally, consider implementing caching mechanisms for performance optimization if you’re dealing with a large number of products.

I recommend further exploring the official Magento 2 Developer Guide and Resource Model Documentation for more advanced techniques in Magento development.

Happy coding, and feel free to reach out if you need further assistance!

Catalog Database Pricing Errors

Our network