Magento get most viewed products from custom phtml

Question

my situation: 1-column.phml

 echo $this->getLayout()
    ->createBlock('catalog/product_list')
    ->setTemplate('page/html/home-block-top.phtml')
    ->toHtml(); 

then home-block-top.phtml

$_productCollection2 = Mage::getResourceModel('reports/product_collection')->addViewsCount();
$_productCollection2->addCategoryFilter( Mage::getModel('catalog/category')->load(87) );
Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($_productCollection2);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($_productCollection2);
$_productCollection2->setPageSize(4)->setCurPage(1);

I get this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'report_table_views.entity_id' in 'on clause'

Someone can help me? Thanks

Answer 1

This is the result:

THERE HAS BEEN AN ERROR PROCESSING YOUR REQUEST

        SELECT COUNT(report_table_views.event_id) AS `views`, `e`.* 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 = 10
         LEFT JOIN `catalog_product_entity_text` AS `_cp_def` ON _cp_def.entity_id = report_table_views.entity_id AND _cp_def.attribute_id = 964 AND _cp_def.store_id = 0
         LEFT JOIN `catalog_product_entity_text` AS `_catalogpermissions_store` ON _catalogpermissions_store.entity_id = report_table_views.entity_id AND _catalogpermissions_store.attribute_id = 964 AND _catalogpermissions_store.store_id = 7 WHERE (report_table_views.event_type_id = 1) AND (IF(_catalogpermissions_store.value_id > 0, _catalogpermissions_store.value, _cp_def.value) NOT REGEXP '(^|,)18(,|$)' OR (_catalogpermissions_store.value IS NULL AND _cp_def.value IS NULL)) GROUP BY `e`.`entity_id` HAVING (COUNT(report_table_views.event_id) > 0) ORDER BY `views` DESC

#0 /usr/www/users/mac/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/www/users/mac/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /usr/www/users/mac/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /usr/www/users/mac/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT COUNT(re...', Array)
#4 /usr/www/users/mac/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT COUNT(re...', Array)
#5 /usr/www/users/mac/lib/Zend/Db/Adapter/Abstract.php(737): Varien_Db_Adapter_Pdo_Mysql->query('SELECT COUNT(re...', Array)
#6 /usr/www/users/mac/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT COUNT(re...', Array)
#7 /usr/www/users/mac/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(1047): Varien_Data_Collection_Db->_fetchAll('SELECT COUNT(re...')
#8 /usr/www/users/mac/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(871): Mage_Eav_Model_Entity_Collection_Abstract->_loadEntities(false, false)
#9 /usr/www/users/mac/lib/Varien/Data/Collection.php(741): Mage_Eav_Model_Entity_Collection_Abstract->load()
#10 /usr/www/users/mac/app/design/frontend/default/sm_flipshop/template/page/html/home-block-top.phtml(23): Varien_Data_Collection->getIterator()
#11 /usr/www/users/mac/app/code/core/Mage/Core/Block/Template.php(241): include('/usr/www/users/...')
#12 /usr/www/users/mac/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/defaul...')
#13 /usr/www/users/mac/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#14 /usr/www/users/mac/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#15 /usr/www/users/mac/app/design/frontend/default/sm_flipshop/template/page/1column.phtml(131): Mage_Core_Block_Abstract->toHtml()
#16 /usr/www/users/mac/app/code/core/Mage/Core/Block/Template.php(241): include('/usr/www/users/...')
#17 /usr/www/users/mac/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/defaul...')
#18 /usr/www/users/mac/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#19 /usr/www/users/mac/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#20 /usr/www/users/mac/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#21 /usr/www/users/mac/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#22 /usr/www/users/mac/app/code/core/Mage/Cms/Helper/Page.php(137): Mage_Core_Controller_Varien_Action->renderLayout()
#23 /usr/www/users/mac/app/code/core/Mage/Cms/Helper/Page.php(52): Mage_Cms_Helper_Page->_renderPage(Object(Mage_Cms_PageController), '2')
#24 /usr/www/users/mac/app/code/core/Mage/Cms/controllers/PageController.php(45): Mage_Cms_Helper_Page->renderPage(Object(Mage_Cms_PageController), '2')
#25 /usr/www/users/mac/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Cms_PageController->viewAction()
#26 /usr/www/users/mac/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('view')
#27 /usr/www/users/mac/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#28 /usr/www/users/mac/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#29 /usr/www/users/mac/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#30 /usr/www/users/mac/index.php(92): Mage::run('', 'store')
#31 {main}

Answer 2

I don't think you can use those filters in a reports collection if you use addViewsCount. I guess if you need addViewsCount you could create a product collection out of the product ids of the report, and then apply the filters. Something like this:

$mostViewed = Mage::getResourceModel('reports/product_collection')->addViewsCount();
$productIds = array();

foreach ($mostViewed as $product) {
   $productIds[] = $product->getId();
}

// Load a collection of the most viewed products and then apply the desired filters
$_productCollection2 = Mage::getModel('catalog/product')->getCollection()->addAttributeToFilter('entity_id', array('in' => $productIds));
$_productCollection2->addCategoryFilter( Mage::getModel('catalog/category')->load(87) );
Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($_productCollection2);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($_productCollection2);
$_productCollection2->setPageSize(4)->setCurPage(1);

I think something like that might work, not sure if there are better ways though. I hope it helps!