How to get products with inventory greater than 0 and In Stock in Magento

How to get products with inventory greater than 0 and In Stock in Magento

October 15, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to get products with inventory greater than 0 and In Stock in Magento

Getting Products with Inventory Greater Than 0 and “In Stock” in Magento

As a senior developer working with e-commerce platforms like Magento, I often encounter queries related to retrieving specific products based on inventory levels and stock statuses. In this post, we’ll explore how to get products that have an inventory greater than 0 and are marked as “In Stock.”

Understanding the Problem Scenario

You currently have a product collection setup in Magento using the following code:

$_productCollection = $product->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('visibility', 4) // Only catalog, search visibility
    ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED))
    ->setCurPage(1)
    ->setPageSize(12);

This code retrieves all enabled products that are visible in the catalog and search. However, it does not filter based on inventory levels or stock status.

The Solution: Filtering by Inventory and Stock Status

To achieve your requirement of getting products with an inventory greater than 0 and marked as “In Stock,” you need to add two additional filters to your product collection setup:

  1. Inventory Greater Than 0: Magento stores the inventory quantity in the cataloginventory_stock_item table for each product. You can use the following attribute filter to retrieve products with a quantity greater than 0:
->addAttributeToFilter(
    'cataloginventory_stock_item.qty',
    array('gt' => 0)
)
  1. Stock Status “In Stock”: The stock status is managed by the cataloginventory_stock_status table and can be filtered using the following attribute filter:
->addAttributeToFilter(
    'cataloginventory_stock_status.stock_status',
    array('eq' => 1) // 1 represents "In Stock"
)

Combining these two filters with your existing code, you get:

$_productCollection = $product->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('visibility', 4) // Only catalog, search visibility
    ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED))
    ->addAttributeToFilter(
        'cataloginventory_stock_item.qty',
        array('gt' => 0)
    )
    ->addAttributeToFilter(
        'cataloginventory_stock_status.stock_status',
        array('eq' => 1) // 1 represents "In Stock"
    )
    ->setCurPage(1)
    ->setPageSize(12);

Best Practices and Further Optimization

When working with large product collections, consider the following best practices to optimize your performance:

  • Use addAttributeToFilter instead of addFieldToFilter for better performance.
  • Only select the required attributes using addAttributeToSelect to reduce memory usage.
  • If you need to perform complex queries on a large number of products, consider using Magento’s indexing and EAV (Entity-Attribute-Value) model.

Remember, efficient filtering is key to maintaining a smooth shopping experience for your customers, especially when dealing with large product catalogs.

Conclusion

Retrieving products with specific inventory levels and stock statuses in Magento requires a combination of attribute filters on the product collection. By adding the appropriate filters for inventory quantity greater than 0 and “In Stock” status, you can effectively get the required products.

For more advanced Magento development tips and techniques, I recommend exploring our comprehensive guides at Laravel Company. We cover a wide range of topics from theme customization to API integrations to optimize your e-commerce experience.

Catalog Database Errors

Our network