List active products in a category in Magento with SQL

List active products in a category in Magento with SQL

September 24, 2026 · Illustrated author avatarBy Stefan Bogdanescu
List active products in a category in Magento with SQL

Retrieving Active Products by Category in Magento Using SQL

Hello! I understand your frustration with retrieving active products by category from the Magento database. Let’s break this down step-by-step to ensure you get the correct results.

Understanding the Magento Database Structure

Magento uses a complex EAV (Entity-Attribute-Value) model, which can make querying challenging. The key tables involved in this scenario are:

  1. catalog_category_flat_store_{store_id} - Lists categories for the specified store.
  2. catalog_category_product - Maps category IDs to product IDs.
  3. catalog_product_entity - Contains general information about products.
  4. catalog_product_flat_{store_id} - A flat table containing various attributes for quick access.
  5. catalog_product_entity_int - Stores integer attributes of products, including the status attribute.

The Correct SQL Query

Based on your findings, you’re close to the solution. Here’s an optimized query that retrieves active simple products in a specific category:

SELECT c.category_name, COUNT(DISTINCT p.entity_id) AS total_active_products
FROM catalog_category_flat_store_9 AS c
LEFT JOIN catalog_category_product AS cc ON c.category_id = cc.category_id
LEFT JOIN catalog_product_entity AS p ON cc.product_id = p.entity_id
WHERE p.type_id = 'simple' AND p.status = 1 AND c.category_id = YOUR_CATEGORY_ID
GROUP BY c.category_id, c.category_name;

Let’s dissect this query:

  • We start with the catalog_category_flat_store_9 table to get category details.
  • Join it with the catalog_category_product and catalog_product_entity tables using the appropriate foreign keys.
  • Filter for simple products (p.type_id = 'simple') that are active (p.status = 1).
  • Replace YOUR_CATEGORY_ID with the ID of the category you want to query.
  • Use GROUP BY to ensure we count distinct entities per category.

Optimizing Performance

To optimize performance:

  • Ensure your database is properly indexed. The required indices are typically created during installation, but double-check them periodically.
  • If you’re dealing with a large product catalog, consider using Magento’s built-in flat tables (like catalog_product_flat_9) for faster access to entity data.

Wrapping Up

Retrieving active products by category in Magento requires navigating the complex EAV structure. However, with the proper SQL queries and an understanding of the database schema, you can efficiently extract the required information.

Remember that directly querying the Magento database should be used sparingly, especially for production sites. For most use cases, consider using Magento’s built-in reports or customizing existing ones to fit your needs.

If you’re looking to develop a more robust solution for data reporting and analysis, consider exploring tools like Elasticsearch or integrating with external BI tools. LaravelCompany offers comprehensive guides on Magento development best practices - feel free to check out our resources!

Catalog Database Performance

Our network