1

I am getting error Column not found: 1054 Unknown column 'sales_bestsellers_aggregated_daily.period' in 'field list' when I am trying to export the bestsellers report.

a:5:{i:0;s:114:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_bestsellers_aggregated_daily.period' in 'field list'";i:1;s:2597:"#0 /lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT COUNT(*)...', Array)
#3 /lib/Varien/Db/Adapter/Pdo/Mysql.php(337): Zend_Db_Adapter_Pdo_Abstract->query('SELECT COUNT(*)...', Array)
#4 /lib/Zend/Db/Adapter/Abstract.php(825): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#5 /lib/Varien/Data/Collection/Db.php(217): Zend_Db_Adapter_Abstract->fetchOne(Object(Varien_Db_Select), Array)
#6 /lib/Varien/Data/Collection.php(225): Varien_Data_Collection_Db->getSize()
#7 /lib/Varien/Data/Collection.php(211): Varien_Data_Collection->getLastPageNumber()
#8 /lib/Varien/Data/Collection/Db.php(581): Varien_Data_Collection->getCurPage()
#9 /lib/Varien/Data/Collection/Db.php(623): Varien_Data_Collection_Db->_renderLimit()
#10 /app/code/core/Mage/Sales/Model/Mysql4/Report/Collection/Abstract.php(247): Varien_Data_Collection_Db->load(false, false)
#11 /app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(923): Mage_Sales_Model_Mysql4_Report_Collection_Abstract->load()
#12 /app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(980): Mage_Adminhtml_Block_Widget_Grid->_exportIterateCollection('_exportCsvItem', Array)
#13 /app/code/core/Mage/Adminhtml/controllers/Report/SalesController.php(128): Mage_Adminhtml_Block_Widget_Grid->getCsvFile()
#14 /app/code/core/Mage/Core/Controller/Varien/Action.php(420): Mage_Adminhtml_Report_SalesController->exportBestsellersCsvAction()
#15 /app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(257): Mage_Core_Controller_Varien_Action->dispatch('exportBestselle...')
#16 /app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#17 /app/code/core/Mage/Core/Model/App.php(340): Mage_Core_Controller_Varien_Front->dispatch()
#18 /app/Mage.php(627): Mage_Core_Model_App->run(Array)
#19 /index.php(101): Mage::run('', 'store')
#20 {main}";s:3:"url";s:184:"/index.php/admin/report_sales/exportBestsellersCsv/key/ca21fc853d7ad9e701576e13db4a6e37/filter/ZnJvbT0yJTJGMTclMkYxMiZ0bz0yJTJGMTclMkYxNiZwZXJpb2RfdHlwZT1kYXkmc2hvd19lbXB0eV9yb3dzPTA=/";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:5:"admin";}

Please let me know if anyone has any idea how this can be fixed.
Any help would be appreciated.

1

To fix the issue I have rewrite the Mage_Adminhtml_Block_Report_Sales_Bestsellers_Grid and override method _exportIterateCollection

<?php

class Custom_Reports_Block_Adminhtml_Report_Sales_Bestsellers_Grid extends Mage_Adminhtml_Block_Report_Sales_Bestsellers_Grid {

    public function _exportIterateCollection($callback, array $args) {
        //$originalCollection = clone $this->getCollection();
        $originalCollection = $this->getCollection();
        $count = null;
        $page = 1;
        $lPage = null;
        $break = false;

        while ($break !== true) {
            $collection = $originalCollection;
            $collection->setPageSize($this->_exportPageSize);
            $collection->setCurPage($page);
            $collection->load();
            if (is_null($count)) {
                $count = $collection->getSize();
                $lPage = $collection->getLastPageNumber();
            }
            if ($lPage == $page) {
                $break = true;
            }
            $page ++;

            foreach ($collection as $item) {
                call_user_func_array(array($this, $callback), array_merge(array($item), $args));
            }
        }
    }

}
0

This error occurs when we use Groups,having or dynamic field in the Collection. For the Pagination It will use getSize() to calculate the Total number of Page.

In order to solve this copy and paste following in your grid.php

public function getSelectCountSql()
{
    $this->_renderFilters();

    $countSelect = clone $this->getSelect();
    $countSelect->reset(Zend_Db_Select::ORDER);
    $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
    $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
    $countSelect->reset(Zend_Db_Select::COLUMNS);

    if(count($this->getSelect()->getPart(Zend_Db_Select::GROUP)) > 0) {
        $countSelect->reset(Zend_Db_Select::GROUP);
        $countSelect->distinct(true);
        $group = $this->getSelect()->getPart(Zend_Db_Select::GROUP);
        $countSelect->columns("COUNT(DISTINCT ".implode(", ", $group).")");
    } else {
        $countSelect->columns('COUNT(*)');
    }


    return $countSelect;
}

Note: If you are still getting this error then comment $countSelect->reset(Zend_Db_Select::COLUMNS); line in getSelectCountSql function. However this will add some overhead as we are considering all the columns while calculating collection size.

1
  • I already have this code and but I am still facing the issue. Then I have tried commenting $countSelect->reset(Zend_Db_Select::COLUMNS); and after commenting the issue persists. – Anshu Mishra Feb 17 '16 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.