Build an sql query for all enabled products in magento database

Build an sql query for all enabled products in magento database

July 31, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Build an sql query for all enabled products in magento database

Fetching Enabled Products from Magento Database

As a developer working with e-commerce platforms like Magento, it’s common to need direct database access for specific tasks such as grabbing product data or running complex queries across multiple tables. In this post, we’ll provide you with the SQL query needed to fetch enabled products along with their name, price, SKU, and quantity from the Magento database.

Understanding the Magento Table Structure

Before diving into the query, let’s briefly understand the relevant Magento tables:

  1. catalog_product_entity - Contains core product data like name, status, etc.
  2. catalog_product_entity_int - Stores additional integer attributes for products.
  3. catalog_product_entity_decimal - Stores decimal attributes like price.
  4. cataloginventory_stock_item - Holds inventory information including quantity.

Building the SQL Query

Now let’s construct the SQL query:

SELECT
    cpe.entity_id AS product_id,
    cpe.sku,
    cpe.name,
    cpei.value as price
FROM catalog_product_entity AS cpe
INNER JOIN catalog_product_entity_int AS cpei ON cpei.entity_id = cpe.entity_id AND cpei.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'status')
WHERE cpei.value = 1
AND cpei.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code IN ('status'))

This query does the following:

  • Selects required fields: product_id (entity ID), sku, name, and price.
  • Joins the necessary tables using appropriate relationships.
  • Filters products based on their status being enabled (value = 1).
  • Limits attributes to those related to product status.

Fetching Quantity Information

To include quantity in your results, you’ll need to join an additional table:

SELECT
    cpe.entity_id AS product_id,
    cpe.sku,
    cpe.name,
    cpei.value as price,
    csi.qty
FROM catalog_product_entity AS cpe
INNER JOIN catalog_product_entity_int AS cpei ON cpei.entity_id = cpe.entity_id AND cpei.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'status')
INNER JOIN cataloginventory_stock_item AS csi ON csi.product_id = cpe.entity_id
WHERE cpei.value = 1
AND cpei.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code IN ('status'))

Conclusion

In this post, we’ve shown you how to fetch enabled products along with their name, price, SKU, and quantity directly from the Magento database using SQL queries.

Remember that direct database access should be done judiciously, preferably in a controlled environment like a development or staging site. Always backup your data before running such queries, especially when updating records.

For more advanced Magento database operations and performance optimizations, consider checking out Laravel Company’s Magento services.

This concludes our guide on fetching enabled products from the Magento database using SQL queries. We hope you found this information helpful!

Catalog Database Pricing

Our network