Get all descendants in one query?

Question

I'm creating an e-commerce site where I have the category_id in the URL and based on the category_id I'll query all the children and grandchild (if found) so I can display products only related to that category_id. This query is really cool because it doesn't care about how many parent's it has ... it just looks for all the parents until it doesn't find any more.

The problem I'm having is my query will only get parent and grand parents (if found), but I cannot seem out how to make this query get all children rows or grandchildren if found. This query was pulled from here, so I did not write the query and it's hard for me to make the correct changes ... I tried all day yesterday.

Edit This query will also NOT get ALL the related children and grandchildren (if found). Where I want ALL children and grand children (if found).

Edit Here's the SQL Fiddle where my data can be found.

SELECT T2.category_id, T2.category_name 
FROM (SELECT @r AS _id, (SELECT @r := parent 
FROM categories 
WHERE category_id = _id) AS parent 
FROM (SELECT @r := 182)vars, categories h 
WHERE @r <> 0) T1 
JOIN categories T2 
ON T1._id = T2.category_id 
ORDER BY category_id ASC

Here's my table schema:

category_id | category_name | parent

Hope this might help :: http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries — Sashi Kant, Jan 27 '13 at 15:11
Hey Sashi, that's close, but I want to achieve this on the database level and not the application level. — Mike, Jan 27 '13 at 15:13
@mike care enough to provide us with some sample data and expected results based on that? =) — bonCodigo, Jan 27 '13 at 15:14
Also, you may want to check out http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/. the user Quassnoi can do this and I hope he takes a look. — Mike, Jan 27 '13 at 15:15
@Mike perhaps this [query is too simple per say](http://sqlfiddle.com/#!2/e8c9a/6), however it seems to go down to the level that you need. Please comment. — bonCodigo, Jan 27 '13 at 15:28
@ bonCodigo - I made an SQL Fiddle using my data http://sqlfiddle.com/#!2/b8a979/2. Also, I'm thinking I need to add a where clause so I can query from a specific `category_id`. — Mike, Jan 27 '13 at 16:30
What is the desired result for the given table (in sqlfiddle)? Can you add that to your question? — inhan, Jan 27 '13 at 16:59
As long as you have either a parent or a child pointer in each row, you can assemble the complete hierarchy(eg: all descendants) in your host programming language. An alternative if you have a finite and reasonable maximum depth `n`, is to just just use `n` left joins in the query. — goat, Jan 27 '13 at 17:06
@mike could you please explain your code? I am not able to find any documentation to understand it. — Ashutosh Nigam, Aug 22 '17 at 12:20

score 1 · Answer 1 · answered Jan 28 '13 at 10:01

There is an alternate way to accomplish this, but it requires a second hierarchy table.

The additional table contains one row per parent-direction relationship, as in:

category_id category_name parent
   1           cat1          1
   2           cat2          1
   3           cat3          1
   4           cat4          2
   5           cat5          3
   6           cat6          5

category_id ancestor
   1           1
   2           1
   3           1
   4           1
   4           2
   5           1
   5           3
   6           1
   6           3
   6           5

This table can be maintained by your code or by a trigger. It makes selecting an entire hierarchy trivial and very fast, but adds some maintenance (addition/deletion) overhead, so you'll need to consider the long-term costs vs overhead.

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

If you know in advance the maximum possible amount of depth like @rambocoder indicated…

SELECT
    p.`category_id`,p.`category_name` AS 'parent',
    c.`category_name` AS 'child',
    gc.`category_name` AS 'grandchild'
FROM (SELECT * FROM `categories` WHERE `parent` IS NULL) p
INNER JOIN `categories` c
ON c.`parent`=p.`category_id`
INNER JOIN `categories` gc
ON gc.`category_id`=c.`category_id`
WHERE p.`category_id`=120
ORDER BY c.`category_id`, gc.`category_id`

Otherwise if you're not sure how many levels deep the result might get, then you will need to write a stored procedure.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 27 '13 at 17:32

inhan

7,394
2
24
35

I appreciate your time, the query in my post will actually work with any level. For example, if you run the query 181 you will get 2 levels. If you run it with 182 you will get 3 levels and if you run it with 119 you will get 1 level. The problem is it get's the hierarchy upward and I need it downward. – Mike Jan 27 '13 at 17:54
What is the maximum level? I mean, can there be a grand grand child, for example? – inhan Jan 27 '13 at 18:45
The maximum level is dynamic, it can have grandchildren and beyond. – Mike Jan 27 '13 at 18:47
You will need a proc for that provided the depth is unknown. – inhan Jan 27 '13 at 18:50
In your example you would be correct, but the first query in this article http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ does not require functions to achieve this. It's the same query I'm using in my Fiddle. A lot of people say it MUST require a function, but I'm currently doing it with one query and no function. The problem is it gets the hierarchy from the insertion point (category_id = 181 or whatever) to the parent. I need it to go from the insertion point downward to the lowest level, whether it's a child, grandchild, grandgrandchild. – Mike Jan 27 '13 at 18:57
Well I stand corrected, ascending does NOT require a function, but ascending DOES require a function. Thank you inhan for your help, patience, and understanding. – Mike Jan 27 '13 at 19:52
So the next question is, would it be better to create the loop at the application level or MySQL? – Mike Jan 27 '13 at 19:53
If you decide to do it in the application level then you will probably need to get (almost) all the data from the db and then process it by loops. Otherwise you can still have a function or a procedure in your db and execute that. It's up to you, actually. – inhan Jan 27 '13 at 23:58

Get all descendants in one query?

2 Answers2