How to write a MYSQL query that will return children nested under parents?

Question

I don't know if what I'm asking is even possible, but here's my situation. I have a table structured somewhat like this:

+--------------------------------------------------+
|   id   |   parent_id   |   name   |   category   | ....
+--------------------------------------------------+
|    0   |       -1      |   item0  |      1       |
|    1   |        0      |   item1  |      1       |
|    2   |        0      |   item2  |      1       |
|    3   |        2      |   item3  |      1       | 
|    4   |        2      |   item4  |      1       | 
|    5   |       -1      |   item5  |      1       | 
+--------------------------------------------------+

A parent_id of -1 will mean it is a "base" item with no parent. Each item will have more columns of information. I need to somehow output all items in a category nested like the following:

item0 => item1    
      => item2
            => item3
            => item4  
item5  

I don't know if that makes sense or not, but hopefully it does!

The only way I can think of doing this is making a query to get all of the "base" items (querying for rows with parent_id = -1) then iterate through every resulting row, querying for rows that have their parent_id equal to the current row's id, then repeating the process going deeper and deeper until there aren't any more children for a base item.

Is there a better way?

Thanks!!

Answer 1

It is not possible in pure SQL.

SQL is intended to work with relational data not trees (hierarchical data).

You can represent tree in an SQL schema, however you won't be a able to result in a tree as you intend to do.

The only way to do is to get an usable result by making as many join as level you're storing.

Your current schema may support multiple level, however, it will be very difficult to manage more than one or two level.

You may be interested in Nested Set Model or Managing hierarchical data in mysql

There are some implementation of the Nested Set like this one to work with Doctrine 2

Answer 2

This is not possible in pure SQL and it is one of the aspects of the relational model that generates most criticism.

I would recommend you to read the links on this post: SQL "tree-like" query - most parent group

And also, if your application relies too much on this, I would suggest you to take a look at some non-relational databases that can represent this kind of data way better, such as MongoDB ( www.mongodb.org )

Answer 3

I hope i understood well your question(it's pretty late here and i've just come from a bar), if i didnt, just correct me and i'll rewrite my answer.

From the scenario given, i guess there's another parent table, isn't there?

Lets imagine it's attributes are id and name. Children table is the given one by you (w/o unnecessary attributes).

mysql> insert into parent(name) values ('petr'),('tomas'),('richard');


mysql> insert into children(name,parent_id) values('michal',1),('tomas',1),('michal');


mysql> select parent.id,parent.name,children.name from parent left join children on parent.id = children.parent_id;

+----+---------+--------+
| id | name    | name   |
+----+---------+--------+
|  1 | petr    | michal |
|  1 | petr    | tomas  |
|  2 | tomas   | NULL   |
|  3 | richard | michal |
+----+---------+--------+

In order to do this multiple time (parent got child who got child who got child etc..) You can accomplish that by using multiple joins.

mysql> select parent.id,parent.name as Parent,children.name as Child,children2.name as Child2 from parent left join children on parent.id = children.parent_id left join children2 on children.id = children2.parent_id;
+----+---------+--------+--------+
| id | Parent  | Child  | Child2 |
+----+---------+--------+--------+
|  1 | petr    | michal | NULL   |
|  1 | petr    | tomas  | dan    |
|  1 | petr    | tomas  | pavel  |
|  2 | tomas   | NULL   | NULL   |
|  3 | richard | michal | michal |
+----+---------+--------+--------+

If i either didnt answer what you asked or you need further explanation let me know ;]

Regards,

Releis