Custom SQL In Product Grid

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?

Answers


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'
     )
);

you can use like below

here is simple example which you can apply to your collection

$collection = Mage::getResourceModel('catalog/product_collection')

                    ->addAttributeToSelect('name')
                    ->addAttributeToSelect('sku')
                    ->addAttributeToSelect('price')
                    ->addAttributeToSelect('status')
                    ->addAttributeToSelect('visibility')
                    ->addAttributeToFilter('type_id', array('eq' => 'simple'))
                    ->addFieldToFilter('status', Mage_Catalog_Model_Product_Status::STATUS_ENABLED)
                    ->addAttributeToFilter('visibility', array('neq' => 1));


   $collection->getSelect()->join(array('abvinv' => "advancedinventory"), "e.entity_id = abvinv.product_id", array('abvinv.*'))

here i am assuming that product_id column which is available in advancedinventory table

Hope this will sure work for you.


The name of the table should be as defined for the magento getResourceModel. eg : catalog/category, directory/country_name...

If it does not work, you can still try to edit the select with $collection->getSelect()->joinLeft(...).


Need Your Help

How to handle overflow on long

c++

I am working with an API that provides some data in a variable of type long. The value of this variable is increasing and will rollover multiple times, unfortunately there is no way to modify the A...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.