3

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
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()
;
  • Filter by status:STATUS_ENABLED will give enabled product. I need Out of stock products – Arun Karnawat Jan 31 '17 at 10:16
  • I have removed addAttributeToFilter and added joinField method call, does it work? – Rendy Eko Prastiyo Jan 31 '17 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 – Arun Karnawat Jan 31 '17 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. – Rendy Eko Prastiyo Feb 2 '17 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. – Rendy Eko Prastiyo Feb 2 '17 at 8:06
2

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);
  • this is working fine and giving all out of stock product including Not Visible Individually – Kanhaiya lal Apr 10 '18 at 18:52
0

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,

  • 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 – Arun Karnawat Jan 31 '17 at 11:32

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.