Custom SQL In Product Grid

Question

How can I add a custom SQL call in the product grid.

This is what I have so far:

$collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('sku')
        ->addAttributeToSelect('name')
        ->addAttributeToSelect('attribute_set_id')
        ->addAttributeToSelect('type_id');

$collection->joinField(
            'quantity_in_stock',
            'advancedinventory',
            'quantity_in_stock',
            'product_id=entity_id',
            'advancedinventory.place_id=1',
            'inner'
        );

$this->addColumn('quantity_in_stock',
        array(
            'header'=> Mage::helper('catalog')->__('Custom Column'),
            'width' => '80px',
            'type' => 'number',
            'index' => 'quantity_in_stock'
    ));

but this doesn't seem to work, I need to get the value from the table advancedinventory where product_id is the id of that entity and place_id is always equal to 1.

Could anyone provide any help at all?

Answer 1

Solved it,

To fix it I had to make the collection this:

$collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('sku')
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('attribute_set_id')
            ->addAttributeToSelect('type_id')
            ->joinField('quantity_in_stock', 'mage_advancedinventory', 'quantity_in_stock', 'product_id=entity_id', 'place_id=1', 'left');`

$this->addColumn('quantity_in_stock',
     array(
            'header'=> Mage::helper('catalog')->__('Custom Column'),
            'width' => '80px',
            'type' => 'number',
            'index' => 'quantity_in_stock'
     )
);