Magento 2 get categories by store Query

Magento 2 get categories by store Query

August 28, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento 2 get categories by store Query

Magento 2: Retrieving Store-Specific Categories via Database Query

In Magento 2, store-specific categories are managed through a combination of tables that handle category data, store assignment, and attribute values. The incorrect query you shared attempts to retrieve categories based on the store_id field from the catalog_category_entity_int table alone, which leads to inaccurate results across all stores.

Understanding Magento 2’s Category Structure

Magento 2 employs a multi-tiered approach to categorization that involves several key tables:

  1. catalog_category_entity: Stores core category data like name, description, and meta tags.
  2. catalog_category_entity_int: Contains integer-type attribute values for categories.
  3. catalog_category_entity_text: Holds text-based attribute values.
  4. catalog_category_entity_decimal: For decimal attributes.
  5. catalog_category_entity_varchar: Handles string attributes.
  6. catalog_category_entity_datetime: Stores date and time attributes.

Retrieving Store-Specific Categories

To fetch all enabled categories specifically for a given store, you must join the required tables correctly. Here’s an improved query that retrieves category data based on your requirements:

SELECT cce.entity_id AS category_id,
       cce.name,
       cce.sort_order,
       ca.store_id
FROM catalog_category_entity cce
JOIN catalog_category_entity_int cafei ON cce.entity_id = cafei.entity_id AND cafei.attribute_id = 46 AND cafei.value = 1
JOIN core_store_group csrg ON cce.group_id = csrg.group_id
JOIN core_store ca ON ca.group_id = csrg.group_id;

This query performs the following:

  • Selects necessary fields from the catalog_category_entity table (category ID, name, and sort order).
  • Joins with the catalog_category_entity_int table to filter categories using the enable/disable attribute (attribute_id = 46) and set them as enabled (value = 1).
  • Links to the store structure via core_store_group and core_store tables based on the category’s group ID.

Important Considerations

  • The query above returns all stores where a specific category is assigned. To get categories only for one particular store, replace ca.store_id with your desired store ID in the WHERE clause.
  • Always ensure you have proper database permissions and backups before running complex queries on your production environment.
  • For more advanced implementations, consider using Magento’s built-in API or repository methods to retrieve category data, which handle object relationships and caching optimally.

Conclusion

Retrieving store-specific categories requires a thorough understanding of Magento 2’s multi-table architecture. By joining the necessary tables correctly and filtering based on the required attributes (in your case, enable status), you can obtain accurate results for each store.

Remember that direct database queries should be used judiciously in production environments. Whenever possible, leverage Magento’s API or repository methods to ensure data integrity, security, and performance optimizations provided by the framework.

For more advanced Magento 2 database optimization techniques and best practices, I recommend exploring Laravel Company’s comprehensive guides on Magento 2 development.

This query should provide you with an effective way to fetch store-specific categories for your specific use case.

Magento 2 Catalog Database Orders

Our network