I want to get the list of category id's with category name through sql. how can i do that?

  • Show what you have tried so far – Black Aug 27 '19 at 9:29
  • @Black nothing for now – Nafisa Aug 27 '19 at 9:45

Try this:

SET @NAME_ATTR_ID = (SELECT attribute_id FROM eav_attribute WHERE attribute_code='name' AND entity_type_id=3);

  catalog_category_entity cce
  catalog_category_entity_varchar ccev
  AND ccev.attribute_id=@NAME_ATTR_ID
  #AND ccev.store_id=0 
  AND ccev.value LIKE '%gear%'

Please check the below SQL

SELECT * FROM `catalog_category_entity` LEFT JOIN catalog_category_entity_varchar ON catalog_category_entity.entity_id = catalog_category_entity_varchar.entity_id WHERE catalog_category_entity_varchar.attribute_id = 45


  • cool, this worked, how can i get this into csv . i am running this query through command line – Nafisa Aug 27 '19 at 9:50

It fetch all the category Ids, their names, url key and their parent id

SELECT DISTINCT cc.entity_id as category_id, cc.value as title, cc1.value as 
urlkey ,cce.parent_id as parent_id ,0 as top,level as `column`,position as 
sort_order,1 as status,created_at as date_added,updated_at as date_modified 

FROM catalog_category_entity_varchar cc 
JOIN catalog_category_entity_varchar cc1 ON cc.entity_id=cc1.entity_id   
JOIN catalog_category_entity_int cc_int ON cc1.entity_id=cc_int.entity_id 
join eav_attribute as att on att.`attribute_id` = cc.`attribute_id`
JOIN eav_entity_type ee ON att.entity_type_id=ee.entity_type_id JOIN catalog_category_entity cce ON cc.entity_id=cce.entity_id 
WHERE cc.attribute_id in (select distinct attribute_id from eav_attribute where attribute_code ='name')
   AND cc1.attribute_id in (select attribute_id from eav_attribute where attribute_code ='url_path')
and cc_int.attribute_id in (select attribute_id from eav_attribute where attribute_code ='is_active')
and cc_int.value = 1
and ((cce.parent_id = 2 and cce.children_count > 1) or cce.parent_id > 2)       
#AND ee.entity_model = 'Magento\Catalog\Model\ResourceModel\Category'
order by cce.parent_id asc, 
cce.position asc;

I hope this will help

  • getting this error Unknown column 'cc.Row_id' in 'field list' – Nafisa Aug 27 '19 at 9:48
  • @Nafisa it is working at my end. I tested this query as well. Maybe you have case sensitive issue cc.Row_id to cc.row_id so I update my query for you. – Muhammad Hasham Aug 27 '19 at 10:03
  • same error Unknown column 'cc.row_id' in 'field list' – Nafisa Aug 27 '19 at 10:30
  • Ok I got it. It's version issue. I am using magento enterprise 2.2.5. I just replace the row_id with entity_id.Now it will work for you as well. Check my updated query – Muhammad Hasham Aug 27 '19 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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