Querying In-Stock Products by Category ID in Magento 2
As an experienced developer, I understand that retrieving specific product data efficiently is crucial for optimizing performance and enhancing user experience in your e-commerce store. In this post, we’ll walk through how to query all in-stock products by category ID in Magento 2, while also addressing a common requirement: fetching only the parent IDs if simple products have stock.
Understanding the Query
Let’s first examine the provided SQL query:
SELECT p.entity_id as product_id, p.sku, c.is_in_stock
FROM catalog_product_entity as p
INNER JOIN cataloginventory_stock_item as c
ON p.entity_id = c.product_id
This query joins two primary tables: catalog_product_entity (holding general product information) and cataloginventory_stock_item (containing stock availability data). The join is based on the matching product IDs.
Incorporating Category Filter
To filter products by category, we need to introduce another table: the catalog_category_product association table. This table connects a product with its respective categories.
Here’s an enhanced version of your query that includes this filtering:
SELECT p.entity_id as product_id, p.sku, c.is_in_stock
FROM catalog_product_entity as p
INNER JOIN cataloginventory_stock_item as c ON p.entity_id = c.product_id
INNER JOIN catalog_category_product as cc ON p.entity_id = cc.product_id
WHERE cc.category_id = <CATEGORY_ID>
Replace <CATEGORY_ID> with the actual ID of the category you want to target.
Fetching Parent IDs Only (If Configured)
To fetch only parent product IDs when their simple variants have stock, we need a slightly more complex query. This involves joining additional tables and checking for configured (parent) products:
SELECT DISTINCT p.entity_id as product_id
FROM catalog_product_entity as p
INNER JOIN cataloginventory_stock_item as c ON p.entity_id = c.product_id AND c.is_in_stock = 1
LEFT JOIN eav_attribute AS a ON a.attribute_code = 'simple_parent'
WHERE (
(p.type_id IN ('configurable') AND (SELECT COUNT(*) FROM catalog_product_entity_super_link WHERE product_id = p.entity_id) > 0)
OR
(p.type_id NOT IN ('configurable'))
)
AND EXISTS (
SELECT * FROM eav_attribute_option_value AS av WHERE av.option_id IN (
SELECT a.attribute_option FROM eav_attribute_option as o
INNER JOIN eav_attribute AS a ON a.attribute_id = o.attribute_id AND a.attribute_code = 'simple_parent'
WHERE p.entity_id = a.value
)
)
AND EXISTS (
SELECT * FROM catalog_category_product AS cc WHERE cc.category_id = <CATEGORY_ID>
);
This query first identifies all in-stock parent products or non-configurable ones. It then checks if these parents have simple variants (through the catalog_product_entity_super_link table) and fetches them.
Conclusion
In this post, we’ve explored how to craft SQL queries that efficiently retrieve in-stock products by category ID in Magento 2. By understanding the underlying database schema and using appropriate joins, you can create targeted and performant queries for your store’s product management needs.
Remember, these queries are just examples; always ensure you’re following best practices for security and performance optimization when working with your actual data. If you need more advanced assistance or want to learn about other Magento 2 development aspects, feel free to explore our Magento 2 tutorials at LaravelCompany.com.