How To Get The Most Viewed Categories In Magento

How To Get The Most Viewed Categories In Magento | apptha

Magento by default, has options to pull the most viewed products report easily from the report module. On my knowledge, there are no options to pull the report of mostly viewed categories. I have done a research on that and successfully pulled the report by adding in a few simple Magento codes.  I would like to share my programming work with you and believe that it might be a great resource to Magento developers.

The basic idea is to get the products in the respective categories, get their view counts and add the sum of the count. This will give the view count of the categories. By comparing the view count of all categories, we can get the Most Viewed Categories.

Here we are going to use the Mage_Catalog_Model_Category model for getting the category list’s and Mage_Reports_Model_Resource_Report_Product_Viewed_Collection model for getting the product view count.

So first lets get the list of all categories
*get selected stores root catalog

$root_id = Mage::app()->getWebsite(true)->getDefaultStore()->getRootCategoryId();
$category_model = Mage::getModel('catalog/category'); //get category model
$root_category = $category_model->load($root_id); //load root catalog
$all_ids = $root_category->getChildren(); //this will give all the active subcategory of the Root Catalog
$root_category_id = explode(',', $all_ids);//getting all the id in array format
        foreach ($root_category_id as $id) { //looping through the root id's
             $sub_category_id[$id] = explode(',',$category_model->load($id)->getChildren());
             //load main category and get its subcategory in array format
             foreach ($sub_category_id[$id] as $value) {//looping through the subcategory id's
                    $this->getMostViewedProductscategory($value); //sending the category id to the function



Okay, now we have the list of all category id’s. Send it to the report module and the most viewed count
*#function getMostViewedProductscategory

public function getMostViewedProductscategory($id){
	$storeId = Mage::app()->getStore()->getId();//to get the store id
	$c_id = Mage::getModel('catalog/category')->load($id);//to load the category and send it to the addCategoryFilter in the collection process this will not take the category id directly so only we need to load the category
	 $products = Mage::getResourceModel('reports/product_collection')//report model to get the view counts
	                     ->addAttributeToSelect('*') //to select all the fields
	                     ->addViewsCount()//here is where we get the view counts of products collection and add to the array
	                     ->addCategoryFilter($c_id);//this selects only the given category
	Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($products);//filtering visible products
	Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($products);//filtering visible products in the category
	$count = 0;
	foreach ($products as $_product) {// now we loop through the products collection
		$count += $_product->getViews();//add the all the products view count in the given category

Now, we have the category id and its view count. You can either return it and do your calculation or store it in separate table so we could use it any time.

Here I have created a table (category_view_count) to store it

$resource = Mage::getSingleton('core/resource');//Get the resource model
$write = $resource->getConnection('core_write');//Retrieve the write connection
$tPrefix = (string) Mage::getConfig()->getTablePrefix(); //table prefix code
$category_view_count = $tPrefix . 'category_view_count';//add the prefix to the table name
$result = $write ->fetchAll("SELECT id,view_count,status FROM $category_view_count WHERE `category_id` = $id ");//to check whether the view category is present or not
		//if result is empty then category is not present so we insert it int the table
		// I have a field called status in the table to know whether the category is enabled or diavled or removed
		return $write ->query("INSERT INTO $category_view_count (`id`, `category_id`, `view_count`, `status`) VALUES (NULL, '$id', '$count', '0')");
		// here I am checking whether the view count for the given category view count is changed or not. If changed and the status is enabled then it will update the count or else it will do nothing ,this is done because to reduce the execution time
	if($result[0]['view_count']!=$count && $result[0]['status'] != 2){
		 return $write ->query("UPDATE $category_view_count SET `view_count` = '$count' WHERE `id` = ".$result[0]['id']."");
		return true;


Finally we have done it, you can run this code often and keep updating the view counts to get the accurate results. Hope this code might satisfy your needs! Also, let me know if you have any general queries regarding this.