4

How to get out of stock product collection using object manager.

I retrieve product collection

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
 
$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');
$collection = $productCollection->create()
            ->addAttributeToSelect('*')
            ->addAttributeToSort('created_at', 'DESC')
            ->setPageSize(8)
            ->load();

but how to add out of stock condition to it.

I have tried this solution here but it does not work.

EDIT

Executing Below code gives following error:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

$productCollectionFactory = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');
$productCollection = $productCollectionFactory
    ->create()
    ->addAttributeToSelect('*')
    ->addAttributeToSort('created_at', 'DESC')
    // use this if you want to filter by quantity = 0
    ->joinField('qty', 'cataloginventory_stock_item', 'product_id=entity_id', 'cataloginventory_stock_item.qty=0')
    // use this if you want to filter by is_in_stock = false
    ->joinField('is_in_stock', 'cataloginventory_stock_item', 'product_id=entity_id', 'cataloginventory_stock_item.is_in_stock=0')
    ->setPageSize(8)
    ->load();

Error

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054
 Unknown column 'at_qty.cataloginventory_stock_item.qty' in 'on clause' in D:\xa
mpp\htdocs\projects\magentoSite\vendor\magento\zendframework1\library\Zend\Db\State
ment\Pdo.php:228
Stack trace:
#0 D:\xampp\htdocs\projects\magentoSite\vendor\magento\zendframework1\library\Zend\
Db\Statement\Pdo.php(228): PDOStatement->execute(Array)
#1 D:\xampp\htdocs\projects\magentoSite\vendor\magento\framework\DB\Statement\Pdo\M
ysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#2 D:\xampp\htdocs\projects\magentoSite\vendor\magento\zendframework1\library\Zend\
Db\Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)

#3 D:\xampp\htdocs\projects\magentoSite\vendor\magento\zendframework1\library\Zend\
Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 D:\xampp\htdocs\projects\magentoSite\vendor\magento\zendframework1\library\Zend\
Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT COUNT(
DI...', Array)
#5 D:\xampp\htdocs\proj in D:\xampp\htdocs\projects\magentoSite\vendor\magento\zend
framework1\library\Zend\Db\Statement\Pdo.php on line 235
1

3 Answers 3

6
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

$productCollectionFactory = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');
$productCollection = $productCollectionFactory
    ->create()
    ->addAttributeToSelect('*')
    ->addAttributeToSort('created_at', 'DESC')
    ->joinField('stock_item', 'cataloginventory_stock_item', 'qty', 'product_id=entity_id', 'qty=0')
    ->setPageSize(8)
    ->load()
;
9
  • Filter by status:STATUS_ENABLED will give enabled product. I need Out of stock products Jan 31, 2017 at 10:16
  • I have removed addAttributeToFilter and added joinField method call, does it work? Jan 31, 2017 at 10:24
  • getting following error: PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'at_qty.cataloginventory_stock_item.stock_id' in 'on clause' in D:\xampp\htdocs\projects\magentoSite\vendor\magento\zendframework1\library\Zend\Db\ Statement\Pdo.php:228 Jan 31, 2017 at 10:34
  • Are you sure you have installed the latest version of Magento 2? If yes, are you sure you have cataloginventory_stock_item table? It's created by magento/module-catalog-inventory module. I'm running Magento 2.1.2 and that code works like a charm. Feb 2, 2017 at 2:37
  • 1
    I have updated the code. It's filtered by qty=0. If you want to filter by is_in_stock=0, change the join field to ->joinField('stock_item', 'cataloginventory_stock_item', 'is_in_stock', 'product_id=entity_id', 'is_in_stock=0'). To be clear, use only one of the filters (qty=0 OR is_in_stock=0), not both. Feb 2, 2017 at 8:06
4

By default in product collection join the cataloginventory_stock_status table so "stock_status_index.stock_status = 1" added into the query.

To get the only out of stock products, first we need to remove cataloginventory_stock_status join and than filter again.

$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');
$collection = $productCollection->create();
$collection->setFlag('has_stock_status_filter', true);
$collection = $collection->addAttributeToFilter('entity_id', array('from' => $start ,'to' => $end))->addAttributeToSelect(array('name', 'sku'))
->addAttributeToSort('created_at', 'DESC')
->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')
    ->addFieldToFilter('stock_status', ['eq' => 0])
->load();

Important part of this code is:

$collection->setFlag('has_stock_status_filter', true);
1
  • this is working fine and giving all out of stock product including Not Visible Individually Apr 10, 2018 at 18:52
1

You will get full collection from your code so just find your out of stock Item using condition if (!$_product->isSaleable()) then print Product data,

1
  • 1
    it is not a wise option if store have lots products, it will consume to much computation time, I am looking for a better approch Jan 31, 2017 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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