CODEWITHSUNDEEP
mysqlsqlhierarchical-data
Mike
Mike

Reputation: 12819

Get All Parents of Hierarchical Category in MySQL

I have a table that looks like the following

cat_id  | name            |   parent_id |   Level
-------------------------------------------------
1         cat1                0             1
2         subcat1             1             2
3         subcat1-subcat      2             3

I am wondering what is the most efficient way to get the parent categories of cat_id 3, so my result set will look like this

cat_id  | name            |   parent_id |   Level
--------------------------------------------------
1         cat1                0             1
2         subcat1             1             2

Upvotes: 0

Views: 2412

Answers (2)

PHP Avenger
PHP Avenger

Reputation: 1801

I was having the same issue, but unfortunately there is no way to do this in single query. so you have to either write a function or a stored procedure which can get all parent categories. algo will be as follows

**childs** = 3
Loop (not done)
 Get immediate parents of **childs**, 
 save(concat) them in a *return* variable
 update **childs** = immediate parents
REPEAT

return will contain all parents(flat) of given category

1) FUNCTION : in case of function you will return a string value as function can not return more than 1 values so your result will be some thing like "1,2". but again this will not full fill our purpose as we have to use this result in a query 

 SELECT * FROM table_name where id IN ("1,2") ORDER BY LEVEL

so instead we will return our result as a regular expression ;) and that is "^(1|2)$"

 SELECT * FROM tbl_categories WHERE cat_id REGEXP "^(1|2)$" ORDER BY level;

2) STORED PROCEDURE: in case of stored procedure we can prepare a statement dynamically and upon executing that query we will have our required result.

for more detail on stored procedure pleas have a look on following tutorial.

Get all nested parents of a category – MySQL

Upvotes: 0

Ariel
Ariel

Reputation: 26753

You have to do multiple queries. One for each level up you want to go (or write it as a stored procedure).

The most efficient way is not to use what's called the "adjacency list model" (what you are using) and instead switch to "nested sets".

Googling for "nested set" will give you lots of info. It will take you some time to get used to it though, and to write the code for handling it.

Upvotes: 1

Related Questions