Column isn't Sorting in Custom Admin Report [duplicate]

Question

I'm having trouble sorting by a column available_qty in a custom admin report.

My select looks like:

$this->getSelect()->reset()
    ->from(
        array("e" => $this->getTable('catalog_product_entity')),
        "*"
    )
    ->joinLeft(
        $this->getTable('purchaseorder/purchaseorder_product'),
        'product_id = entity_id',
        array(
            'qty_ordered'           => 'qty_ordered',
            'qty_recieved'          => 'qty_received',
            'qty_on_purchase_order' => new Zend_Db_Expr('SUM(qty_ordered) - SUM(qty_received)')
        )
    )
    ->joinLeft(
        array('stock_item' => $this->getTable('cataloginventory/stock_item')),
        'stock_item.product_id = entity_id',
        array(
            'qty_on_hand'        => 'qty',
            'qty_on_sales_order' => 'qty_on_sales_order'
        )
    )
    ->columns(array(
        'available_qty' => new Zend_Db_Expr('qty - qty_on_sales_order') // this is the column I'm trying to order by
    ))
    ->group('e.entity_id');

And when I prepare the columns

$this->addColumn('qty_on_hand', array(
    'header'   => $this->__('Qty On Hand'),
    'index'    => 'qty_on_hand',
    'type'     => 'number',
    'filter'   => false
));

$this->addColumn('qty_on_sales_order', array(
    'header'   => $this->__('Qty On Sales Order'),
    'index'    => 'qty_on_sales_order',
    'type'     => 'number',
    'filter'   => false
));

$this->addColumn('available_qty', array(
    'header'   => $this->__('Qty Available'),
    'index'    => 'available_qty',
    'type'     => 'number',
    'filter'   => false
));

But it obviously isn't sorting. I'm not even seeing the ORDER BY being added to the query. What am I doing wrong?

---

I've also tried

$this->addAttributeToSelect('status')
    ->addAttributeToSelect('vendor_id', 'left')
    ->addAttributeToSelect('name')
    ->addAttributeToSelect('cost', 'left')
    ->joinTable(
        'purchaseorder/purchaseorder_product',
        'product_id=entity_id',
        array(
            'qty_ordered'           => 'qty_ordered',
            'qty_recieved'          => 'qty_received',
            'qty_on_purchase_order' => new Zend_Db_Expr('SUM(qty_ordered) - SUM(qty_received)')
        ), null, 'left'
    )->joinTable(
        'cataloginventory/stock_item',
        'product_id=entity_id',
        array(
            'qty_on_hand'        => 'qty',
            'qty_on_sales_order' => 'qty_on_sales_order',
            'available_qty'      => new Zend_Db_Expr('qty - qty_on_sales_order')
        ), null, 'left'
    );
$this->getSelect()
    ->group('e.entity_id');

But then I get

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cataloginventory_stock_item.qty - qty_on_sales_order' in 'order clause'

You can see that the order statement isn't being formed correctly.

ORDER BY `cataloginventory_stock_item`.`qty - qty_on_sales_order`

Answer 1

Filtering & sorting grid columns

I'll mainly talk about filters here, but a lot of it also applies to sorting.

Background: How are grid filters added to the grids collection?

Grid Filters are applied in Mage_Adminhtml_Block_Widget_Grid::_prepareCollection().
Slightly abbreviated:

$filter   = $this->getParam($this->getVarNameFilter(), null);

if (is_string($filter)) {
    $data = $this->helper('adminhtml')->prepareFilterString($filter);
    $this->_setFilterValues($data);
}
else if ($filter && is_array($filter)) {
    $this->_setFilterValues($filter);
}

The method setFilterValues() then sets the filter data to the individual columns:

$column->getFilter()->setValue($data[$columnId]);
$this->_addColumnFilterToCollection($column);

As you can see, the filter for each column is then also applied to the collection:

protected function _addColumnFilterToCollection($column)
{
    if ($this->getCollection()) {
        $field = ( $column->getFilterIndex() ) ? $column->getFilterIndex() : $column->getIndex();
        if ($column->getFilterConditionCallback()) {
            call_user_func($column->getFilterConditionCallback(), $this->getCollection(), $column);
        } else {
            $cond = $column->getFilter()->getCondition();
            if ($field && isset($cond)) {
                $this->getCollection()->addFieldToFilter($field , $cond);
            }
        }
    }
    return $this;
}

This gives us the following options to specify how to filter:

Ways to specify a filter on a grid column declaration

Filter callback

First, the field to use when filtering. It can be specified using filter_index key in the $this->addColumn() call. If it isn't present, it falls back to the index field.

Then it's possible to specify a callback method using the filter_condition_callback key in the $this->addColumn() call.

$this->addColumn('test', array(
    'header' => 'Test',
    'index' => 'test',
    'filter_condition_callback' => array($this, 'filterCallbackTest')
));

The filter callback method takes two arguments:

public function filterCallbackTest($collection, $column)
{
    $field = $column->getFilterIndex() ? $column->getFilterIndex() : $column->getIndex();
    $value = $column->getFilter()->getValue();
    $collection->getSelect()->having("$field=?", $value); // just as an example 
}

Custom filter class

If there is no callback method specified for the column, the condition is added using

$cond = $column->getFilter()->getCondition();
$this->getCollection()->addFieldToFilter($field , $cond);

This means, if we specify a custom filter class with a custom getCondition()implementation, we once again have another way to hook into the grid filter logic:

$this->addColumn('test2', array(
    'header' => 'Test2',
    'index' => 'test2',
    'filter' => 'my_module/adminhtml_grid_column_filter_test' // Block class factory name
));

Then the custom filter class can extend the core filter which matches our needs as much as possible (for example adminhtml/widget_grid_column_filter_select), and only implements the getCondition() method:

public function getCondition()
{
    $value = $this->getValue();
    return array('finset' => $value); // Just as an example
}

But I'm getting the "Unknown column" exception!

If the column displays the result of an SQL expression (and not a simple column value), in MySQL, you can't simply add it to a WHERE condition. Even if you specify the filter_index field as your expression alias it just won't work.

As a quick fix you can apply the filter using having() instead of where(). In SQL, HAVING conditions are applied to the final result set after all the rest of the query is executed.
This means the query will be slower and use more memory, but it will work without putting in much effort.

What MySQL actually is asking for, is that you specify the same SQL expression you used for the column, again, for the WHERE condition. For example, the following would cause the exception:

SELECT (a - b) AS result_diff FROM test WHERE result_diff > 0;

What MySQL needs is:

SELECT (a - b) AS result_diff FROM test WHERE (a - b) > 0;

The same applies for sorting!

My filter isn't applied!

This happens mostly if the column is added via an event observer.
Since the grid filters are applies while Mage_Adminhtml_Block_Widget_Grid::_prepareCollection() is processed, the column definition has to be added before this method is called.

All the grid hook methods (_prepareColumns, _prepareMassactionBlock and _prepareCollection) are called during the processing of _beforeToHtml(), so we have to use an event that is dispatched before the rendering of the block starts.

Therefore the events adminhtml_block_html_before, core_block_abstract_to_html_before and (if we are working with EAV collection) eav_collection_abstract_load_before simply are called to late.

This leaves us with core_block_abstract_prepare_layout_after to add the column to the grid:

protected function coreBlockAbstractPrepareLayoutAfter(Varien_Event_Observer $observer)
{
    $grid = $observer->getBlock();
    // Check this really is the target grid block
    if ($grid->getType() === 'adminhtml/customer_grid') { // Adjust for target grid

        // Add the attribute as a column to the grid
        $grid->addColumnAfter(
            'my_grid',
            array(
                'header' => $grid->__('Available Qty'),
                'index' => 'available_qty',
                'filter_condition_callback' => array($this, 'filterCallbackTest')
            ),
        'customer_since' // The column after which our column should appear
    );

    // Set the new columns order... otherwise our column would be the last one
    $grid->sortColumnsByOrder();
}