Filtering a joined column

Question

I am creating a stock report in the admin and have everything working so far, except that I can't seem to be able to filter on the joined column.

I have joined the stock information, using the following to grab my collection.

$collection = Mage::getModel('catalog/product')->getCollection()
                ->addAttributeToSelect('name')
                ->addAttributeToSelect('sku')
                ->addAttributeToSelect('price')
                ->setStoreId($storeId);
$collection->addFieldToFilter('type_id', 'simple');

// Add on the stock qty information
$collection->getSelect()->join( array('stock'=>'ccmg_cataloginventory_stock_item'), 'e.entity_id = stock.item_id', array('stock.qty'));

This is causing it to display, but you can't filter or sort the column. I assume because the options aren't being passed back into the join. However, the other columns can be sorted and filtered and the matching data is pulled back and displayed.

I've been searching but most posts are on the Magento forums from 2008, and I'm using 1.6! Any pointers would be great!

Answer 1

After the join, you need to add the joined field to the array _map declared in Varien_Data_Collection_Db, for example:

$this->_map['fields']['stock_qty'] = 'stock.qty';

[edit] As pointed out by @sh4dydud3_88, you can do this:

$collection->addFilterToMap('stock_qty', 'stock.qty');

which will add the field stock_qty for filtering. Then you can filter with

$collection->addFieldToFilter('stock_qty', array('gt', 10));

Another example:

class Company_Mohe_Model_Resource_Im_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
protected function _construct()
{
    $this->_init('mohe/im');
}  


public function joinIhe()
{
    $this->getSelect()->join(array('ihe' => $this->getTable('mohe/ihe')),
                                  'main_table.mic_inst_id = ihe.im_id',
                                  array('ihe_name'=>'name', 'ihe_ifms_id'=>'ifms_id')); 
    //$this->_map['fields'] = array('ihe_name'=>'ihe.name', 'ihe_ifms_id'=>'ihe.ifms_id'); //incorrect method
    $this->addFilterToMap('ihe_name', 'ihe.name'); //correct method, see comment by @sh4dydud3_88                           
    return $this;
} 
}