I want to get all enable categories for specific store from query in Magento 2 database. As I tried from database query from table catalog_category_entity_int

SELECT * FROM `catalog_category_entity_int` WHERE `attribute_id`=46 AND `store_id`=1 GROUP BY `entity_id`

Where 46 is attribute id for enable attribute.

With this query i am getting all categories ids but for all stores categories are same if i change store_id the results are same for all stores

  • Your question needs to be updated as catalog_category_entity_int does not contain any column named entity_id which you are using in GROUP BY clause. Apr 5, 2021 at 9:20

1 Answer 1


First of all the value of the attribute is_active which correctly you found the id 46 can be in one or more rows for the same category.

Lets take an example:

Lets assume the Category with the entity_id = 2.

In the table catalog_category_entity_int for sure there will be one row for the default store_id which is the 0 , if you went to the admin area and changed is_active attribute for another store view or website lets say for the french store view which's id is 1 (this ids are for the table store) then in the table catalog_category_entity_int you will have another row with another value for this attribute. So now you will have 1 row for the default store_id and one for the french store_id.

When your user will be in french store view he will get the value with the store_id=1 if the user will be in any other store view or website the default store_id will be used.

enter image description here

To get the categories for the specific store view for many categories there wont be a value in other store_id except 0 .

So I would suggest to export values for each store view and then combine them to see which one you need.

SELECT * FROM `catalog_category_entity_int` WHERE `attribute_id`=46 AND `store_id`=1 

SELECT * FROM `catalog_category_entity_int` WHERE `attribute_id`=46 AND `store_id`=0 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.