Admin product grid filtered by multi SKUs

Question

I need to find a way to enhance the admin products grid with capability to fill in the SKU field more than one reference.

For example, get copy/paste from an Excel list multiple sku and get the matching list on Magento.

I've already a solution in place based on default grid block rewriting but there is a conflict with a new cleaner extension I want to use with (Enhanced Admin Grids from Magento connect).

What is the best way to achieve this? Rewriting how the default sku field is working or adding a new custom one?

Answer 1

In my opinion adding a new custom clumn is better because of two reasons:

1. The core team may modify the existing sku column any time. Additionally, as you said any extension developer may rewrite this grid or more specific this column given it's a very basic one. If you create your own column probably nobody else knows about it what makes it less likely that somebody will screw around with your column

2. It's hard (if not impossible) to modify the existing columns without rewrites or observer a very generic event and therefore possibly slowing down the page generation.

Having said that and contra-dicting myself there is a way to use a product-grid-specific event and modify the original sku column. You should document this though as it's not very intuitive if you find the code again two years later.

The solution is: observe the event adminhtml_catalog_product_grid_prepare_massaction amd manipulate the already existing column.

1. I'll assume that you created an extension before so I'll skip Magento extension creation basics. I call my example extension Emzee_MultiSkuFilter.

2. Add an observer for adminhtml_catalog_product_grid_prepare_massaction in app/code/local/Emzee/MultiSkuFilter/etc/config.xml:

   <?xml version="1.0"?>
   <config>
       <modules>
           <Emzee_MultiSkuFilter>
               <version>1.0.0</version>
           </Emzee_MultiSkuFilter>
       </modules>
       <adminhtml>
           <events>
               <adminhtml_catalog_product_grid_prepare_massaction>
                   <observers>
                       <emzee_multiskufilter>
                           <class>emzee_multiskufilter/observer</class>
                           <method>adminhtmlCatalogProductGridPrepareMassaction</method>
                       </emzee_multiskufilter>
                   </observers>
               </adminhtml_catalog_product_grid_prepare_massaction>
           </events>
       </adminhtml>
       <global>
           <models>
               <emzee_multiskufilter>
                   <class>Emzee_MultiSkuFilter_Model</class>
                   <resourceModel>emzee_multiskufilter_resource</resourceModel>
               </emzee_multiskufilter>
               <emzee_multiskufilter_resource>
                   <class>Emzee_MultiSkuFilter_Model_Resource</class>
               </emzee_multiskufilter_resource>
           </models>
           <helpers>
               <emzee_multiskufilter>
                   <class>Emzee_MultiSkuFilter_Helper</class>
               </emzee_multiskufilter>
           </helpers>
       </global>
   </config>
   

3. In the observer class app/code/local/Emzee/MultiSkuFilter/Model/Observer.php you receive the grid block. Find the sku column and define a filter condition callback which will be used to query the database when you filter by sku:

   <?php
   
   class Emzee_MultiSkuFilter_Model_Observer
   {
       public function adminhtmlCatalogProductGridPrepareMassaction(Varien_Event_Observer $observer)
       {
           $block = $observer->getEvent()->getBlock();
           $column = $block->getColumn('sku');
           $column->setData('filter_condition_callback', array(
               Mage::getResourceModel('emzee_multiskufilter/filter'), 'multiSkuFilter'
           ));
           return $this;
       }
   }
   

4. In the filter callback app/code/local/Emzee/MultipleSkuFilter/Model/Resource/Filter.php, split up the SKUs and construct your SQL. It's not the most elegant code but it works. By default the SKUs have to be an exact match but you also can search for %test% to use wildcards.

   <?php
   
   class Emzee_MultiSkuFilter_Model_Resource_Filter
   {
       public function multiSkuFilter($collection, $column)
       {
           if (!$value = $column->getFilter()->getValue()) {
               return $this;
           }
   
           /**
            * @var Mage_Catalog_Model_Resource_Product_Collection $collection
            * @var Zend_Db_Select $select
            */
           $select = $collection->getSelect();
           $connection = $collection->getConnection();
           $conditions = array();
           $skus = explode(' ', $value);
           foreach ($skus as $sku)  {
               $condition = $connection->quoteInto('`e`.' . $column->getIndex() . ' LIKE ?', $sku);
               $conditions[] = $condition;
   
           }
   
           $sql = implode(' ' . Zend_Db_Select::SQL_OR . ' ', $conditions);
           $select->where($sql);
           return $this;
       }
   
   }
   

This is what the result looks like: