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 format 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.
Checkout : Untold Features of Magento Stores
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 ->setStoreId($storeId) ->addStoreFilter($storeId) ->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(empty($result)){ //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')"); }else{ // 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'].""); }else{ 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.
I have read “How To Get The Most Viewed Categories In Magento” post and i have a question for you, what do you think about use the log_URL_info table to count the row number of Id’s? so in this way we can have the numbers of Id’s and put all products in a category call “The most view” and show it.
I apologise for my english I’am no american and i’am introducing to magento framework. thanks.
Hi Alan,
As you said you can be able to Query the log_URL_info table to achieve the same ,but there are few problems as there are option to clear the log table when the traffic of the site is high
Not only that table there are few other’s which i have mention below
The following tables are managed by Magento’s Log Cleaning function:
log_customer
log_visitor
log_visitor_info
log_url
log_url_info
log_quote
report_viewed_product_index
report_compared_product_index
report_event
catalog_compare_item
So once cleaned we will not be able to get the data. So its better to use the reports model .