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.
Advertisement
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'