I have a custom column on my sales order grid showing the ordered items sku's. Done by following the guide here. It worked well in previous versions but no longer does work correctly in 1.9.4.3.
When I go to filter the column by any given sky, I get the error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'skus' in 'where clause', query was: SELECT COUNT(DISTINCT main_table.entity_id) FROM
sales_flat_order_grid
ASmain_table
INNER JOINsales_flat_order_item
ONsales_flat_order_item
.order_id=main_table
.entity_id WHERE (skus
LIKE '%tst%')
Backtracing this, the query is from here:
public function getSelectCountSql()
{
$countSelect = parent::getSelectCountSql();
if (Mage::app()->getRequest()->getControllerName() == 'sales_order') {
$countSelect->reset(Zend_Db_Select::GROUP);
$countSelect->reset(Zend_Db_Select::COLUMNS);
$countSelect->columns("COUNT(DISTINCT main_table.entity_id)");
$havingCondition = $countSelect->getPart(Zend_Db_Select::HAVING);
if (count($havingCondition)) {
$countSelect->where(
str_replace("group_concat(`sales_flat_order_item`.sku SEPARATOR ', ')", 'sales_flat_order_item.sku', $havingCondition[0])
);
}
}
return $countSelect;
}
In order to try rectify this I added 'filter_index' => 'sku',
to my addColumnAfter, via a helper function.
This has fixed the above SQL error, but I am now faced with the below issue(s):
Filtering by multiple skus returns no result. i.e,
Filter by
x,z
, even if order has say sku'sx,c,v,z,y
, no result returned.
Also, Filtered result shows only the sku I am filtering by. i.e,
Filter by
x
, will only showx
sku in the column even if the order has say itemsx, y and z
.
How would I get it to work so the sku column shows all skus on the order when filtering and multiple sku filter?
My filter callback:
public function filterSkus($collection, $column)
{
if (!$value = $column->getFilter()->getValue()) {
return $this;
}
$collection->getSelect()->having(
"group_concat(`sales_flat_order_item`.sku SEPARATOR ', ') like ?", "%$value%");
return $collection;
}
The helper function:
public function getSkusColumnParams()
{
return array(
'header' => 'SKUs',
'index' => 'skus',
'filter_index' => 'sku',
'type' => 'text',
'filter_condition_callback' => array(Mage::getSingleton('Atwix_ExtendedGrid_model_observer'), 'filterSkus'),
);
}
Config.xml:
<config>
<modules>
<Atwix_ExtendedGrid>
<version>1.0.1</version>
</Atwix_ExtendedGrid>
</modules>
<global>
<blocks>
<atwix_extendedgrid>
<class>Atwix_ExtendedGrid_Block</class>
</atwix_extendedgrid>
</blocks>
<helpers>
<atwix_extendedgrid>
<class>Atwix_ExtendedGrid_Helper</class>
</atwix_extendedgrid>
</helpers>
<models>
<atwix_extendedgrid>
<class>Atwix_ExtendedGrid_Model</class>
</atwix_extendedgrid>
<sales_resource>
<rewrite>
<order_grid_collection>Atwix_ExtendedGrid_Model_Resource_Sales_Order_Grid_Collection</order_grid_collection>
</rewrite>
</sales_resource>
</models>
</global>
<adminhtml>
<events>
<sales_order_grid_collection_load_before>
<observers>
<atwix_exgrid>
<model>atwix_extendedgrid/observer</model>
<method>salesOrderGridCollectionLoadBefore</method>
</atwix_exgrid>
</observers>
</sales_order_grid_collection_load_before>
</events>
<layout>
<updates>
<atwix_extendedgrid>
<file>atwix/extendedgrid.xml</file>
</atwix_extendedgrid>
</updates>
</layout>
</adminhtml>
filter_condition_callback
is not respected in 1 grid ... M1 tries to filter by an EAV column that does not exist .. and hence we see an error