php - Custom SQL In Product Grid

957

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 tableadvancedinventory whereproduct_id is the id of that entity andplace_id is always equal to 1.

Could anyone provide any help at all?

663

Answer

Solution:

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

Answer

Solution:

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 thatproduct_id column which is available inadvancedinventory table

Hope this will sure work for you.

510

Answer

Solution:

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(...).

People are also looking for solutions to the problem: php - Date value saving as 0000-00-00 00:00:00 into a TIMESTAMP field when the query seems to be in ok format

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.