0

I’m looking for a faster way to retrieve a list of SKUs of "in stock" products.

I’m using this code

$products_collection = \Mage::getResourceModel('catalog/product_collection')->addAttributeToSelect('sku');

\Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($products_collection);

return $products_collection->getColumnValues('sku');

but it’s quite slow, because first it gets all products then filter them by "in stock".

Are there any faster query to execute? Maybe using an inverse style: first get only IDs of "in stock" products then get only their SKUs?

0

Try this

$resource = Mage::getResourceSingleton('catalog/product');
$read = $resource->getReadConnection();
$select = $read->select()
    ->from(array('p' => $resource->getTable('catalog/product')), 'sku')
    ->joinInner(
        array('i' => $resource->getTable('cataloginventory/stock_item')),
        'i.product_id = p.entity_id AND i.is_in_stock=1',
        array()
    );

$skus = $read->fetchCol($select);
1
  • It looks like that the Magento addInStockFilterToCollection() has a more complete ON clause: i.product_id = e.entity_id AND ((i.use_config_manage_stock = 0 AND i.manage_stock = 1 AND i.is_in_stock = 1) OR (i.use_config_manage_stock = 0 AND i.manage_stock = 0) OR (i.use_config_manage_stock = 1 AND i.is_in_stock = 1)), but this one looks enough in my specific case. – Riccardo Jul 6 '17 at 13:44
0

You can try join() and where()

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()->join('cataloginventory_stock_status', 'cataloginventory_stock_status.product_id = e.entity_id', array('stock_status'));
$collection->getSelect()->where("`cataloginventory_stock_status`.`stock_status` = 1");

$collection is loaded with only In Stock items. Hope this helps.

1
  • I just tested it: unfortunately it looks slow as mine. – Riccardo Jul 6 '17 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.