0

Here is the class to rewrite sales order grid adding email column, there is an error for filtering by this email column

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'email' in 'where clause', query was: SELECT COUNT(*) FROM sales_flat_order_grid AS main_table WHERE (email LIKE '%abcd@gmail.com%')

when filtering, no matter how it didn't join the table,

echo select will print

"SELECT main_table.*, ce.email FROM sales_flat_order_grid AS main_table LEFT JOIN customer_entity AS ce ON ce.entity_id = main_table.customer_id"

it shows the collection is joined, but it prints the error message anyway, any one know what is the problem?


    <?php

    class Namespace_Sales_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid {

        protected function _prepareColumns()
        {

            parent::_prepareColumns();

            foreach ($this->getColumns() as $columns) {
                switch($columns->getId()){
                    case 'action':
                        $columns->setTotalsLabel('');
                        break;
                    case 'status':
                        $columns->setFilter('_sales/adminhtml_widget_grid_column_filter_multiselect');
                        $columns->setFilterConditionCallback(array($this, '_addFilterMultiselect'));
                        break;
                    case 'store_id':
                        $columns->setFilter('namespace_sales/adminhtml_widget_grid_column_filter_multiselect');
                        $columns->setFilterConditionCallback(array($this, '_addFilterMultiselect'));
                        break;
                }
            }

            $this->addColumnAfter(
                'email',
                array(
                    'header'   => Mage::helper('sales')->__('Email'),
                    'align'    => 'left',
                    'index'    => 'email',
                    'filter_condition_callback' => array($this, '_filterEmailConditionCallback')
                    //'filter_index'    => 'email',
                ),
                'shipping_name'
            );

            $this->sortColumnsByOrder();

            return $this;
        }

        protected function _filterEmailConditionCallback($collection, $column)
        {

            $select = $collection->getSelect();

            if ($collection) {

                $field = ($column->getFilterIndex()) ? $column->getFilterIndex() : $column->getIndex();

                if ($column->getFilterConditionCallback()) {

                    $select = $collection->getSelect();
                    $cond = $column->getFilter()->getCondition();
                    echo $select;
//die();
                    $collection->addFieldToFilter('email', $cond);


                } else {

                    $cond = $column->getFilter()->getCondition();

                    if ($field && isset($cond)) {
                        $collection->addFieldToFilter($field, $cond);
                    }

                }
            }


            //return $this;
        }   

        protected function _prepareCollection()
        {

            $collection = Mage::getResourceModel(parent::_getCollectionClass());
            $collection->getSelect()->joinLeft(array('ce' => 'customer_entity'),'ce.entity_id = main_table.customer_id', array('email'));
            $this->setCollection($collection);

            return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
        }   

        protected function _prepareMassactionColumn(){

            parent::_prepareMassactionColumn();
            $this->_columns['massaction']->setTotalsLabel($this->__('Totals'));
            return $this;
        }

        public function getCountTotals()
        {
            return $this->getCollection()->count()>0;
        }

        public function getTotals()
        {

            $result = array();

            if($this->getCollection()->count()>0){
                $fields = array(
                    'base_grand_total' => array(), //actual column index, see _prepareColumns()
                    'grand_total' => array(),
                );

                $baseCurrencyCode = Mage::app()->getStore()->getBaseCurrencyCode(); 

                foreach ($this->getCollection() as $item) {
                    foreach($fields as $field=>$value){
                        $codeCurrency = $item->getOrderCurrencyCode();
                        if(strpos($field, 'base') !== false) {
                            $codeCurrency = $baseCurrencyCode;
                        }

                        if(!isset($fields[$field][$codeCurrency])){
                            $fields[$field][$codeCurrency] = 0;
                        }

                        //Base prices don't include currency. Must be converted in the current currency
                        if(strpos($field, 'base') !== false){
                            $total = Mage::helper('directory')->currencyConvert($item->getData($field), $baseCurrencyCode, $codeCurrency);
                        }
                        else {
                            $total = $item->getData($field);
                        }
                        $fields[$field][$codeCurrency]+=$total;
                   }
                }

                foreach ($fields as $field => $currencies) {
                    foreach ($currencies as $codeCurrency => $total) {
                        $result[$field][] = Mage::app()->getLocale()->currency($codeCurrency)->toCurrency($total);
                    }
                    $result[$field] = implode('<br />',$result[$field]);
                }
            }

            return new Varien_Object($result);
        }

        protected function _addFilterMultiselect($collection,$column)
        {

            if (($column->getId() == 'store_id' || $column->getId() == 'status') && $column->getFilter()->getValue()) {
                $_inNin = explode(',', $column->getFilter()->getValue());
                $inNin = array('in');

                foreach ($_inNin as $k => $v) {
                    if (is_string($v) && strlen(trim($v))) {
                        $inNin[] = trim($v);
                    }
                }

                if (count($inNin)>1 && in_array($inNin[0], array('in', 'nin'))) {
                    $in = $inNin[0];
                    $values = array_slice($inNin, 1);
                    $collection->addFieldToFilter('main_table.'.$column->getId(), array($in => $values));
                }
            }
        }

    }
  • $this->addColumnAfter( 'email' ... Your problem seems to be coming from here, is this attempted to select from the database with the email column on that table that does not possess it? – LM_Fielding Jul 17 '17 at 10:24
  • 1
    the collection is skipping the join query – hkguile Jul 17 '17 at 10:26
  • And the email address is only on the table it's joining (customer_entity) not on the one it is currently selecting on (due to the lack of a join) – LM_Fielding Jul 17 '17 at 10:29
  • can you please add _filterEmailConditionCallback code here. i will provide soluction – Rama Chandran M Jul 17 '17 at 17:22
  • @Rama _filterEmailConditionCallback was already included in above source – hkguile Jul 18 '17 at 2:30
-2

Please Try this below Solution

kindly visit below URL, download and install this "OrdersPlus", no need to add custom code. I have tested It's working magento 1.7 to 1.9.3.1 with email filter as well.

https://github.com/gsafcik/OrdersPlus

I Hope It's helpful for you.

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.