cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query for category ID

SQL query for category ID

Hello,

 

in the table "catalog_category_entity" are the path of the categories. In column "path" I have the ids:

Example:

1/2/3/16

 

Now I need the ID from the category "trousers":

"Default Categorie/clothes/men/trousers"

 

In the above example it is "16" ... but how can I get this with an SQL query?

Where are the name are stored?

 

Maybe someone can help?

 

Thank you!

4 REPLIES

Re: SQL query for category ID

Hi @georg_filler,

 

Maybe this query can help you a little bit:

 

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea  WHERE ea.entity_type_id = 3 AND attribute_code = 'name')
AND cev.value = 'Root Catalog'

Some tips/clues:

 

  • entity_type_id = 3 means Category
  • attribute_code is the attribute to seach for into the category attributes. If the data type is different you should check which catalog_cateogry_entity_? table you will need.
  • cev.value = 'Root Catalog' is the category name I'm looking for. If you change the attribute code, you should use a valid or possible value there.

 

--
If you've found one of my answers useful, please give "Kudos" or "Accept as Solution"

Re: SQL query for category ID

Hello,

 

many thanks to your answer ... but not sure if I unterstood irt correctly:

My given categorie path text is:

"Default Category/clothes/men/trousers"

 

I need the category ID from "trousers"

 

This don´t work:

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea  WHERE ea.entity_type_id = 3 AND attribute_code = 'trousers')
AND cev.value like 'Default Category'

 

Thank you!!

Re: SQL query for category ID

Hi @georg_filler,

 

That's becasue you changed the attribute code insted the value you're looking for. Try again with:

 

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea 
WHERE ea.entity_type_id = 3 AND attribute_code = 'name') AND cev.value like 'trousers'
--
If you've found one of my answers useful, please give "Kudos" or "Accept as Solution"

Re: SQL query for category ID

Hello,

 

ok, this work.

 

But I have the complete path:

"Default Category/clothes/men/trousers"

 

trousers is not unique so I have to give the complete path:

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea 
WHERE ea.entity_type_id = 3 AND attribute_code = 'name')
AND cev.value like 'Default Category/clothes/men/trousers'

Maybe you have a solution for this?

 

Thank you!