Magento. Update stock status of products via direct SQL

mage_update_stock_status_of_products_via_direct_sql.markdown

Update stock status of products via direct SQL

In this case set out_of_stock to all products what is_in_stock (stock item status) and discontinued (code of yes/no attribute) via direct sql for speed up it and do without load product collection. This is good example for use direct SQL queries(select/update) using Varien_Db_Adapter_Interface.

Code of class below:

  <?php

class {Vendor}_{Extension}_Model_Cron 
{
    const ATTRIBUTE_CODE  ='discontinued';

    /**
     *
     * @var array 
     */
    protected $productIds = array();
    
    /**
     * Set `is_in_stock` to `0` for all products with `discotinued` attribute value = `1`
     */
    public function setDiscontinuedOutOfStock() 
    {
        /* @var $select Varien_Db_Select */
        $select = $this->_getReadAdapter()->select()
            ->from(
                array('cpe' => $this->_getResource()->getTableName('catalog/product')),
                array('cpe.entity_id')
            )
            ->join(
                array('csi' => $this->_getResource()->getTableName('cataloginventory/stock_item')),
                sprintf(
                        'csi.product_id = cpe.entity_id AND csi.is_in_stock = %s', 
                        Mage_CatalogInventory_Model_Stock::STOCK_IN_STOCK
                    ),
                array()
            )
            ->joinLeft(
                array('ea' => $this->_getResource()->getTableName('eav/attribute')),
                'cpe.entity_type_id = ea.entity_type_id',
                array()
            )
            ->join(
                array('cpei' => $this->_getResource()->getTableName('catalog_product_entity_int')),
                sprintf(
                        'cpe.entity_id = cpei.entity_id AND cpei.attribute_id = ea.attribute_id AND ea.attribute_code = "%s" AND cpei.value = %s', 
                        self::ATTRIBUTE_CODE, 
                        1
                    ),
                array()
            )
        ;
        
        $selectResult = $this->_getReadAdapter()->fetchAll($select);
        
        foreach($selectResult as $item) {
            $this->productIds[] = $item['entity_id'];
        }
        
        if( ! empty($this->productIds) ) {
            $this->_getWriteAdapter()->update(
                $this->_getResource()->getTableName('cataloginventory/stock_item'), 
                array('is_in_stock' => Mage_CatalogInventory_Model_Stock::STOCK_OUT_OF_STOCK), 
                array('product_id in (?)' => $this->productIds) 
            );

            Mage::log($this->productIds, null, 'update_stock_of_discontinued_products.log', true);
            Mage::getSingleton('index/indexer')->getProcessByCode('cataloginventory_stock')->reindexAll();
        }
        
        Varien_Profiler::stop(__METHOD__);
    }
    
    /**
     * 
     * @return Mage_Core_Model_Resource
     */
    private function _getResource()
    {
        return Mage::getSingleton('core/resource');
    }
    
    /**
     * Create write connection
     * 
     * @return Varien_Db_Adapter_Interface
     */
    private function _getWriteAdapter() 
    {
        return $this->_getResource()->getConnection('core_write');
    }
    
    /**
     * Create read conenction
     * 
     * @return Varien_Db_Adapter_Interface
     */
    private function _getReadAdapter() 
    {
        return $this->_getResource()->getConnection('core_read');
    }
    
}