Magento-2.1 – SQL Query to Get All Simple Product Quantities and SKUs

magento-2.1stock

I just don't know how to get all simple product's stock and SKU information with SQL Query. I just need to get something like this

SKU       Quantity   Stock Status
---       --------   ------------
454645       50           1
545878       15           1

Any help please.

Best Answer

Use following query :

"SELECT cpe.sku, csi.qty FROM catalog_product_entity cpe RIGHT JOIN cataloginventory_stock_item csi ON cpe.entity_id = csi.item_id GROUP BY cpe.sku ORDER BY cpe.sku ASC";

You can check in stock or out of stock according to the returned quantity

Related Solutions

Magento – Magento – foreach simple product show name, sku and stock quantity

This should work to quickly grab everything you want. There may be a better way to do it.

<?php 
// Load Magento
require_once '../app/Mage.php';
umask(0);
Mage::app('default');


//get the collection filter the simple products & join the cataloginventory/stock_item table
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('sku')->addAttributeToFilter('type_id', 'simple')->joinField(
    'qty',
    'cataloginventory/stock_item',
    'qty',
    'product_id=entity_id',
    '{{table}}.stock_id=1',
    'left'
) ;
?>
<html>
<head></head>
<body>
<table>
<?php 
foreach ($collection as $product) {
    echo "<tr><td>".$product->getSku()."</td><td> ".(int)$product->getQty()."</td></tr>";
};




?>
</table>
</body></html>

Magento – How to get all products collection in a store

In the method Mage_Adminhtml_Block_Sales_Order_Create_Search_Grid::_prepareCollection there is this line that filters only in stock products: Mage::getSingleton('catalog/product_status')->addSaleableFilterToCollection($collection);
But changing this might lead to other issues since there are verifications when placing the order for example in Mage_Adminhtml_Block_Sales_Order_Create_Items_Grid::getItems.
But it's a good place to start.

Best Answer

Related Solutions

Magento – Magento – foreach simple product show name, sku and stock quantity

Magento – How to get all products collection in a store

Related Topic