Magento - Adminhtml Trying to search in the product grid by custom column

Question

I am using Magento 1.9.2 and i am rewriting the products grid table.

I have done a copy from the original Grid.php and created this:

/app/code/local/Mage/Adminhtml/Block/Catalog/Product/Grid.php and here is what it contains:

<?php
class Mage_Adminhtml_Block_Catalog_Product_Grid extends Mage_Adminhtml_Block_Widget_Grid
{

    public function __construct()
    {
        parent::__construct();
        $this->setId('productGrid');
        $this->setDefaultSort('entity_id');
        $this->setDefaultDir('DESC');
        $this->setSaveParametersInSession(true);
        $this->setUseAjax(true);
        $this->setVarNameFilter('product_filter');

    }

    protected function _getStore()
    {
        $storeId = (int) $this->getRequest()->getParam('store', 0);
        return Mage::app()->getStore($storeId);
    }

    protected function _prepareCollection()
    {
        $store = $this->_getStore();
        $collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('sku')
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('attribute_set_id')
            ->addAttributeToSelect('type_id');

        if (Mage::helper('catalog')->isModuleEnabled('Mage_CatalogInventory')) {
            $collection->joinField('qty',
                'cataloginventory/stock_item',
                'qty',
                'product_id=entity_id',
                '{{table}}.stock_id=1',
                'left');
        }
        if ($store->getId()) {
            //$collection->setStoreId($store->getId());
            $adminStore = Mage_Core_Model_App::ADMIN_STORE_ID;
            $collection->addStoreFilter($store);
            $collection->joinAttribute(
                'name',
                'catalog_product/name',
                'entity_id',
                null,
                'inner',
                $adminStore
            );
            $collection->joinAttribute(
                'custom_name',
                'catalog_product/name',
                'entity_id',
                null,
                'inner',
                $store->getId()
            );
            $collection->joinAttribute(
                'status',
                'catalog_product/status',
                'entity_id',
                null,
                'inner',
                $store->getId()
            );
            $collection->joinAttribute(
                'visibility',
                'catalog_product/visibility',
                'entity_id',
                null,
                'inner',
                $store->getId()
            );
            $collection->joinAttribute(
                'price',
                'catalog_product/price',
                'entity_id',
                null,
                'left',
                $store->getId()
            );
        }
        else {
            $collection->addAttributeToSelect('price');
            $collection->joinAttribute('status', 'catalog_product/status', 'entity_id', null, 'inner');
            $collection->joinAttribute('visibility', 'catalog_product/visibility', 'entity_id', null, 'inner');
        }

        $this->setCollection($collection);

        parent::_prepareCollection();
        $this->getCollection()->addWebsiteNamesToResult();
        return $this;
    }

    protected function _addColumnFilterToCollection($column)
    {
        if ($this->getCollection()) {
            if ($column->getId() == 'websites') {
                $this->getCollection()->joinField('websites',
                    'catalog/product_website',
                    'website_id',
                    'product_id=entity_id',
                    null,
                    'left');
            }
        }
        return parent::_addColumnFilterToCollection($column);
    }

    protected function _prepareColumns()
    {
        $this->addColumn('entity_id',
            array(
                'header'=> Mage::helper('catalog')->__('ID'),
                'width' => '50px',
                'type'  => 'number',
                'index' => 'entity_id',
        ));
        $this->addColumn('name',
            array(
                'header'=> Mage::helper('catalog')->__('Name'),
                'index' => 'name',
        ));

        $store = $this->_getStore();
        if ($store->getId()) {
            $this->addColumn('custom_name',
                array(
                    'header'=> Mage::helper('catalog')->__('Name in %s', $store->getName()),
                    'index' => 'custom_name',
            ));
        }

        $this->addColumn('type',
            array(
                'header'=> Mage::helper('catalog')->__('Type'),
                'width' => '60px',
                'index' => 'type_id',
                'type'  => 'options',
                'options' => Mage::getSingleton('catalog/product_type')->getOptionArray(),
        ));

        $sets = Mage::getResourceModel('eav/entity_attribute_set_collection')
            ->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId())
            ->load()
            ->toOptionHash();

        $this->addColumn('set_name',
            array(
                'header'=> Mage::helper('catalog')->__('Attrib. Set Name'),
                'width' => '100px',
                'index' => 'attribute_set_id',
                'type'  => 'options',
                'options' => $sets,
        ));

        $this->addColumn('sku',
            array(
                'header'=> Mage::helper('catalog')->__('SKU'),
                'width' => '80px',
                'index' => 'sku',
        ));

        $this->addColumn('number',
            array(
                'header'=> Mage::helper('catalog')->__('Поръчка №'),
                'width' => '50px',
                'index' => 'entity_id',
                'renderer'  => 'Mage_Adminhtml_Block_Catalog_Product_Renderer',
        ));

        $store = $this->_getStore();
        $this->addColumn('price',
            array(
                'header'=> Mage::helper('catalog')->__('Price'),
                'type'  => 'price',
                'currency_code' => $store->getBaseCurrency()->getCode(),
                'index' => 'price',
        ));

        if (Mage::helper('catalog')->isModuleEnabled('Mage_CatalogInventory')) {
            $this->addColumn('qty',
                array(
                    'header'=> Mage::helper('catalog')->__('Qty'),
                    'width' => '100px',
                    'type'  => 'number',
                    'index' => 'qty',
            ));
        }

        $this->addColumn('visibility',
            array(
                'header'=> Mage::helper('catalog')->__('Visibility'),
                'width' => '70px',
                'index' => 'visibility',
                'type'  => 'options',
                'options' => Mage::getModel('catalog/product_visibility')->getOptionArray(),
        ));

        $this->addColumn('status',
            array(
                'header'=> Mage::helper('catalog')->__('Status'),
                'width' => '70px',
                'index' => 'status',
                'type'  => 'options',
                'options' => Mage::getSingleton('catalog/product_status')->getOptionArray(),
        ));

        if (!Mage::app()->isSingleStoreMode()) {
            $this->addColumn('websites',
                array(
                    'header'=> Mage::helper('catalog')->__('Websites'),
                    'width' => '100px',
                    'sortable'  => false,
                    'index'     => 'websites',
                    'type'      => 'options',
                    'options'   => Mage::getModel('core/website')->getCollection()->toOptionHash(),
            ));
        }

        $this->addColumn('action',
            array(
                'header'    => Mage::helper('catalog')->__('Action'),
                'width'     => '50px',
                'type'      => 'action',
                'getter'     => 'getId',
                'actions'   => array(
                    array(
                        'caption' => Mage::helper('catalog')->__('Edit'),
                        'url'     => array(
                            'base'=>'*/*/edit',
                            'params'=>array('store'=>$this->getRequest()->getParam('store'))
                        ),
                        'field'   => 'id'
                    )
                ),
                'filter'    => false,
                'sortable'  => false,
                'index'     => 'stores',
        ));

        if (Mage::helper('catalog')->isModuleEnabled('Mage_Rss')) {
            $this->addRssList('rss/catalog/notifystock', Mage::helper('catalog')->__('Notify Low Stock RSS'));
        }

        return parent::_prepareColumns();
    }

    protected function _prepareMassaction()
    {
        $this->setMassactionIdField('entity_id');
        $this->getMassactionBlock()->setFormFieldName('product');

        $this->getMassactionBlock()->addItem('delete', array(
             'label'=> Mage::helper('catalog')->__('Delete'),
             'url'  => $this->getUrl('*/*/massDelete'),
             'confirm' => Mage::helper('catalog')->__('Are you sure?')
        ));

        $statuses = Mage::getSingleton('catalog/product_status')->getOptionArray();

        array_unshift($statuses, array('label'=>'', 'value'=>''));
        $this->getMassactionBlock()->addItem('status', array(
             'label'=> Mage::helper('catalog')->__('Change status'),
             'url'  => $this->getUrl('*/*/massStatus', array('_current'=>true)),
             'additional' => array(
                    'visibility' => array(
                         'name' => 'status',
                         'type' => 'select',
                         'class' => 'required-entry',
                         'label' => Mage::helper('catalog')->__('Status'),
                         'values' => $statuses
                     )
             )
        ));

        if (Mage::getSingleton('admin/session')->isAllowed('catalog/update_attributes')){
            $this->getMassactionBlock()->addItem('attributes', array(
                'label' => Mage::helper('catalog')->__('Update Attributes'),
                'url'   => $this->getUrl('*/catalog_product_action_attribute/edit', array('_current'=>true))
            ));
        }

        Mage::dispatchEvent('adminhtml_catalog_product_grid_prepare_massaction', array('block' => $this));
        return $this;
    }

    public function getGridUrl()
    {
        return $this->getUrl('*/*/grid', array('_current'=>true));
    }

    public function getRowUrl($row)
    {
        return $this->getUrl('*/*/edit', array(
            'store'=>$this->getRequest()->getParam('store'),
            'id'=>$row->getId())
        );
    }
}

The custom code i've added in the Grid.php is this:

    $this->addColumn('number',
        array(
            'header'=> Mage::helper('catalog')->__('Поръчка №'),
            'width' => '50px',
            'index' => 'entity_id',
            'renderer'  => 'Mage_Adminhtml_Block_Catalog_Product_Renderer',
    ));

I have created and rendered also, i saw this in another answer:

<?PHP
class Mage_Adminhtml_Block_Catalog_Product_Renderer extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{
      public function render(Varien_Object $row)
      {

        $productId = $row->getData($this->getColumn()->getIndex());
            $orders = array();
            $collection = Mage::getResourceModel('sales/order_item_collection')
                ->addAttributeToFilter('product_id', array('eq' => $productId))
                ->load();
            foreach($collection as $orderItem) {
                $orders[$orderItem->getOrder()->getIncrementId()] = $orderItem->getOrder();
            }

            $first_key = key($orders);  
            return $first_key;


      }
}

I have added the additional column so i can display in every row in which order ID the product is purchased. I have no problems. Everything is correct, but the problem comes when i try to search with this custom column.

The order ID is displayed correctly in the column but can not be searched by order ID.

Where is my mistake, why it is not working and how can i fix it?

Thanks in advance!

score 1 · Answer 1 · answered Jul 25 '16 at 11:36

1

You are unable to search your custom_column in grid because you have just used renderer which returns order_id in run time and prepare your columns. That's it. What magento grid search do, it filters the loaded collection with "search text".

For example:- you make a search for order_id = 10000901, your search result returns null because order_id is not present in collection.

So you should join sales_flat_order & sales_flat_order_item with your product table in order to get increment_id in collection. Then sorting & searching both will work perfectly.

answered Jul 25 '16 at 11:36

Laxman Singh

391
2
10

Thank you so much for the answer. I am aware i must do that but i do not know how. Can you please help me? – Venelin Jul 25 '16 at 12:02
Can anyone please help. Any guide ? – Venelin Jul 25 '16 at 18:53
Which code to add ? Can you please make an comlete answer. I have searched so many placed and can not resolve it. Thanks in advance! – Venelin Jul 26 '16 at 09:48

score 1 · Answer 2 · answered Jul 26 '16 at 09:49

Please add this code

$collection->getSelect()->joinLeft( 
        array('order_item'=>'sales_flat_order_item'),
        'e.entity_id = order_item.product_id', 
        array('order_item.product_id','order_item.order_id')
        );
$collection->getSelect()->joinLeft( 
        array('order'=>'sales_flat_order'),
        'order_item.order_id = `order`.entity_id', 
        array('order.increment_id')
        );
$collection->getSelect()->group('e.entity_id');

before $this->setCollection($collection); in method _prepareCollection() of your /app/code/local/Mage/Adminhtml/Block/Catalog/Product/Grid.php

and

update column code as below

$this->addColumn('increment_id',
    array(
        'header'=> Mage::helper('catalog')->__('Order Id'),
        'width' => '100px',
        'type'  => 'number',
        'index' => 'increment_id',
));

Thanks for the answer. Now the search works but redirects me to the orders page. Why ? Is it possible to stay on the products grid and display the results there ? — Venelin, Jul 26 '16 at 10:01
Even the search is not working. It is just redirecting to the sales grid page. Can you please help me out ? Thanks. — Venelin, Jul 26 '16 at 10:22
can you please make this column filterable ? Is it even possible ? — Venelin, Aug 10 '16 at 15:48

Magento - Adminhtml Trying to search in the product grid by custom column

2 Answers2