7

How would I format a sql query to get a list of all category (id, name and url_path) from my magento database.

Alternatively if you know the tables I need to do this, a list and some sort of explanation as to what I need to do would be a great help.

16

Using @mpaepper answer I was able to get a query that seems to be pulling the correct information back. I was able to test it on two different magento databases and each looked right. Anyway here it is.

  SELECT DISTINCT cc.entity_id as id, cc.value as path, cc1.value as name    
  FROM catalog_category_entity_varchar cc    
  JOIN catalog_category_entity_varchar cc1 ON cc.entity_id=cc1.entity_id    
  JOIN eav_entity_type ee ON cc.entity_type_id=ee.entity_type_id
  JOIN catalog_category_entity cce ON cc.entity_id=cce.entity_id
  WHERE cc.attribute_id = '57' AND cc1.attribute_id = '41' AND ee.entity_model = 'catalog/category'; 

I probably didn't need to join eav_entity_type but I will probably be using this query again on different versions of magento so I think this will help keep the query reusable.

  • 2
    Good job, to make it database independent, I would recommend also joining in the eav_attribute table to get the attribute_ids. Use attribute_code with 'name' and 'url_path' since the attribute_code will be the same between installs, the ID will not :) – mpaepper Jun 6 '14 at 12:22
  • 1
    i need to export from db like , sku,name,category_name, price, how can i get that – Gem Oct 7 '16 at 9:10
  • How can i get only main categories not sub-categories? – Gem Jul 30 '18 at 8:48
14

Ok, so here are the tables and what you will need to do (I will let you do the MySQL join fun for yourself ;)):

catalog_category_entity is the base table which has your category ID (entity_id).

You then need to identify the attribute IDs for name and url_path from the table eav_attribute.

In my case, the attribute_code name for entity_type_id 3 (for me 3 is categories, look that up in the table eav_entity_type) has the attribute_id 41. In my case, the attribute_code url_path for entity_type_id 3 has the attribute_id 57.

Both name and url_path are of type varchar, so you will find their values in catalog_category_entity_varchar (filter for attribute_id and entity_id where entity_id is the corresponding ID of the category).

Thus you need to use the catalog_category_entity table and join catalog_category_entity_varchar twice with the entity_id as join condition and specifying the attribute_ids which you can look up. Alternatively, you can do more joins, so you do not look up the IDs before, but join them in.

Have fun! :)

  • hey thanks for the answer, that should get the ball rolling. One question though, as you mentionted attribute_id seems like it can be different per install. Is there some logic behind what is the attribute_id or is it random. – develophper Jun 5 '14 at 15:31
  • Depending on what you're using this for, what about alternatively running a simple select against the flat category table? All 3 pieces of data are there, so if you're just looking to grab this for something like building a spreadsheet every now and then… it's far simpler and will work just as well. – davidalger Jun 5 '14 at 20:52
  • it's for a drupal site that needs to use the category information. This feature will be used regularly so I would like it to be quite robust and always accurate. – develophper Jun 6 '14 at 7:44
  • @ThomasRyan The attribute_id is pretty random. It is an auto-increment key, so depending on your install, the IDs can differ significantly. – mpaepper Jun 6 '14 at 12:20
  • Thanks! Your answer taught me how I should observe the Magento database. – CLOUGH Jun 7 '17 at 7:10
4

To get all main category use the below query

select *
  from catalog_category_flat_store_1
  where level = 2 and
        is_active = 1
  order by parent_id asc, position asc;

Change the level accordingly

  • 1
    This does not work if you don't have the flat categories flag set to yes or if you don't have a website with id 1 – Marius Sep 15 '15 at 11:32
  • @Marius You are correct if flat is not set to yes, we have to use something like below select a.*,b.* from catalog_category_entity a, catalog_category_entity_varchar b where a.entity_id = b.entity_id and b.attribute_id in (select attribute_id from eav_attribute where entity_type_id = 9 and (attribute_code = "name" or attribute_code = "image")) and level =2 order by parent_id asc, position asc – arun-r Sep 15 '15 at 12:26
4
SELECT DISTINCT cc.entity_id as category_id, cc.value as name, cc1.value as 
url_path ,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_entity_type ee ON cc.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 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 = 'catalog/category' order by cce.parent_id asc, 
cce.position asc;
  • @ arun : how can i export category name instead of category id – Gem May 6 '17 at 12:33
  • @JeevaRathinam cc1.value as title contains category name – arun-r May 8 '17 at 4:59
  • @arun-jamhub : how to export sku, category path(name not id) – user51361 May 24 '17 at 10:16
1

In Magento 2, entity_type_id is not available in catalog_category_entity_varchar table, so I have to google and find the intermediate table,eav_attribute, to get what I need. Entity_id is also replace with Row_Id in Magento 2

SELECT DISTINCT cc.Row_id as category_id, cc.value as image, cc1.value as 
title ,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.Row_id=cc1.Row_id   
JOIN catalog_category_entity_int cc_int ON cc1.Row_id=cc_int.Row_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.Row_id=cce.Row_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 want to thank you all to help me get what I need. :)

0

To get Magento category data directly from the data base, I use this:

select
   p.entity_id  as entity_id,

   if(pp.level = 1,
      n.value,
      concat_ws (' / ', pn.value, n.value)) as name,

   date(p.created_at) as created_at

from
   catalog_category_entity p

   -- name
   left join catalog_category_entity_varchar n on
      p.entity_id = n.entity_id and n.attribute_id = 41

   -- status
   left join catalog_category_entity_int s on
      p.entity_id = s.entity_id and s.attribute_id = 42 and
      s.store_id = 0

   -- parent
   left join catalog_category_entity pp on p.parent_id = pp.entity_id

   -- parent name
   left join catalog_category_entity_varchar pn on
      pp.entity_id = pn.entity_id and pn.attribute_id = 41 and
      pn.store_id = 0

where
   s.value = 1 and -- status is active
   p.level >= 2 

order by
   2
;

Attribute_ids differ from system to system. Here I'm using 41, and 42.

In order to determine the correct attribute_ids for category name and category status from a Magento site, I use:

select
   p.entity_id,
   a.attribute_id,
   a.frontend_label as attribute,
   av.value

from
   catalog_category_entity p

   left join catalog_category_entity_{datatype} av on
      p.entity_id = av.entity_id

   left join eav_attribute a on
      av.attribute_id = a.attribute_id

where
   p.entity_id = {eid}
;

By substitute {datatype} with 'varchar' you can get the attribute_id for category name, and with 'int' to get the attribute_id for category status. You can substitute {eid} for any category entity_id.

This can be done in an editor or on the command line with sed like so:

Assuming wdb is an alias set to your website mysql database connection like this:

alias wdb='mysql -h<hostname> -u<username> -p<password> <databasename>'

Then you can run

$ cat show_category_attr.sql | sed -e "s/{datatype}/varchar/" -e "s/{eid}/2/" | wdb -t

And get

+-----------+--------------+--------------+------------------+
| entity_id | attribute_id | attribute    | value            |
+-----------+--------------+--------------+------------------+
|         2 |           41 | Name         | Default Category |
|         2 |           49 | Display Mode | PRODUCTS         |
+-----------+--------------+--------------+------------------+

And run

$ cat show_category_attr.sql | sed -e "s/{datatype}/int/" -e "s/{eid}/2/"| wdb -t

And get

+-----------+--------------+----------------------------+-------+
| entity_id | attribute_id | attribute                  | value |
+-----------+--------------+----------------------------+-------+
|         2 |           42 | Is Active                  |     1 |
|         2 |           67 | Include in Navigation Menu |     1 |
+-----------+--------------+----------------------------+-------+

Here you can see the necessary numbers 41 and 42.

The same technique can be used for Magento Products.

0

For Magento 2. To get a full list of category names along with their IDs and URL Paths:

SELECT entity_id AS "CATEGORY ID", ccev1.value as "CATEGORY NAME", CONCAT(ccev2.value, ".html") AS "CATEGORY PATH"
FROM catalog_category_entity AS cce
JOIN catalog_category_entity_varchar AS ccev1 USING (entity_id)
JOIN catalog_category_entity_varchar AS ccev2 USING (entity_id)
JOIN eav_attribute AS ea1 ON ea1.attribute_id = ccev1.attribute_id
JOIN eav_attribute AS ea2 ON ea2.attribute_id = ccev2.attribute_id
WHERE ea1.attribute_code = 'name' AND ea1.entity_type_id = 3 AND ea2.attribute_code = 'url_path' AND ea1.entity_type_id = 3
ORDER BY entity_id

This is ordered by Category ID. If you want to order it by name change "ORDER BY entity_id" to "ORDER BY ccev1.value".

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.