Magento – Join stock inventory resource to the product collection

gridmagento-1

could you please help me join the stock inventory resource to my grid.php?
I would like to edit my grid in order to filter by availability "is in stock".

Tried adding this code, but without joining the resource it will not work.

    $this->addColumn('inventory_stock_availability ',
            array(
                'header' => Mage::helper('catalog')->__('Status'),
                'width' => '100px',
                'index' => 'inventory_stock_availability',
                'type' => 'options',
                'options' => Mage::getSingleton('catalog/inventory_stock_availability ')->getOptionArray(),
            ));

my grid file is just like this one https://github.com/jayelkaake/enhancedgrid/blob/master/app/code/community/TBT/Enhancedgrid/Block/Catalog/Product/Grid.php

Best Answer

Add below code:

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('sku')
    ->addAttributeToSelect('name')
    ->addAttributeToSelect('attribute_set_id')
    ->addAttributeToSelect('type_id')
    ->joinField('qty',
        'cataloginventory/stock_item',
        'qty',
        'product_id=entity_id',
        '{{table}}.stock_id=1',
        'left'
    )->joinTable('cataloginventory/stock_item', 'product_id=entity_id', array('stock_status' => 'is_in_stock'))
    ->addAttributeToSelect('stock_status');

Add below code in function _prepareColumns()

$this->addColumn('stock_status', array(
    'header'=> 'Availability',
    'width' => '60px',
    'index' => 'stock_status',
    'type'  => 'options',
    'options' => array(
        '1' => 'In stock',
        '0' => 'Out of stock'
    )
));

joinTable()

But magento collections have a method joinTable() it took me 45min to fizzle out how it is used. To avoid this for you, I share it.

$productCollection->joinTable(
    array('bonus' => 'mycompany/bonus'), 'product_id=entity_id',
    array('bonus_id' => 'bonus_id')
);

The parameters are:

public function joinTable($table, $bind, $fields = null, $cond = null, $joinType = 'inner')

Table is easy, it is the magento namespace/entity format, which you use in your configuration, resource models and the collection. You can use an array of the format array('alias' => 'namespace/entity')
Bind means, the ON statement in your SQL. This was the hardest part and I will explain it in details later. It is important to have your flat table BEFORE and your EAV table AFTER the equal sign. Don't use main_table. before the attribute. Magento will do this for you. More on this later.
Fields is an array. If you use a string instead, you get this: Warning: Invalid argument supplied for foreach() in /var/www/magento-1.6.2.0/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php on line 775. You can use an array of the format array('field1', 'field2', '...') or array('alias' => 'field1', '...')
Condition is a ** ~~WHERE~~ ON condition** in the SQL.
joinType. I hope you know what it is. But you have only the choice between LEFT and INNER. app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:793

Magento – join 2 collection in custom Grid Magento

Please try with this:

$collection
    ->getSelect()
    ->join(
        array('so' => $this->getTable('sales/order')),
        'main_table.order_id = so.entity_id',
        array('so.grand_total')
    )
    ->join(
        array('soa' => $this->getTable('sales/order_address')),
        "(main_table.order_id = soa.parent_id AND soa.address_type = 'shipping')",
        array('soa.*')
    );

replace order_id with the column name that you used in your table for the order ID.

Also you can view/read this - http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/using_collections_in_magento .

Best Answer

Related Solutions

Magento1 Collection Query – How to Perform a Left Join

joinTable()

Magento – join 2 collection in custom Grid Magento

Related Topic