Magento - query to get active categories in current store

Magento - query to get active categories in current store

August 26, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento - query to get active categories in current store

Retrieving Active Categories in Magento

As an experienced developer, I understand your need to efficiently retrieve active categories from your Magento store through a SQL query. The query you’ve shared returns all categories regardless of their activity status. Let’s optimize this and get the desired results.

Understanding Magento Category Status

In Magento, each category has an ‘is_active’ field in the catalog_category_entity table that determines whether it is active or not. A value of 1 indicates an active category while 0 denotes a non-active one.

The Optimized Query

To get only the active categories from your store, you should join this status field into your query:

SELECT e.entity_id, e.name, e.url_path
FROM catalog_category_flat_store_1 AS f
INNER JOIN catalog_category_entity AS e ON f.entity_id = e.entity_id
WHERE f.level >=3 AND e.is_active = 1 ORDER BY f.parent_id ASC, f.position ASC;

Breaking Down the Query

  • The catalog_category_flat_store_1 table contains pre-calculated data for fast access. It’s specific to your store.
  • We join it with the core catalog_category_entity table using the entity ID as the common field.
  • The WHERE clause filters out inactive categories (where e.is_active = 0). This ensures we only get active ones.
  • The ORDER BY clause sorts the result by parent ID and position, which is usually the desired order for category lists.

Performance Considerations

While this query will return accurate results, it’s important to note that using flat tables (like catalog_category_flat_store_1) can help improve performance significantly compared to directly querying the core system.

If your store isn’t utilizing these pre-calculated tables, I recommend enabling them in your Magento configuration. For details on how to do this, you can refer to my previous post Improving Magento Performance with Flat Tables.

Conclusion

Retrieving active categories from a Magento store requires understanding the store’s data structure and utilizing the right tables. By joining the catalog_category_flat_store_1 table with the core entity table on the entity ID, we can create an efficient query that only returns the desired active categories.

Remember, while SQL queries are powerful tools for retrieving data directly from your database, Magento provides advanced functionalities through its APIs and controllers. Always consider whether a custom query is necessary or if you could leverage existing Magento features to achieve your goals.

I hope this solution helps optimize your category retrieval process. If you have any other development needs or encounter further challenges with your Magento store, feel free to explore more of our Magento development resources for comprehensive guides and best practices.

Catalog Database Performance Themes

Our network