find by name the id of a multi level categorie

my main problem is how can i search by name the id of a given category, let me explain , so ,in my database i have a table called product_category where is stored a 5 level categories. let’s take an example of it:

id	name	parent_id
1	A
2	B	1
3	C	2
4	D
5	A	4
6	B	5
7	C	6

so from this table let’s take these two categories :

• A/B/C
• D/A/B/C

in my table the categories name are redundant but don’t have the same id, now let’s say that i want to search the id of this category (A/B/C) , is it possible in my case ? knowing that there’s two categories named C with different ids , the same with B an A.

i am using python and odoo , but i didn’t find a way to solve this especially that in my data base there is redundant names.

Answer

You can use a recursive query that builds up the full path for each category, then filter on that full path in the final select:

with recursive tree as (
  select id, name, parent_id, name as full_path
  from category 
  where parent_id is null
  union all
  select c.id, c.name, c.parent_id, concat(p.full_path, '/', c.name) as full_path
  from category  c
    join tree p on p.id = c.parent_id
)
select id, name, parent_id
from tree
where full_path = 'A/B/C'