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

Question

I do not know if what I ask is possible, but here is 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 value of -1 will mean that it is a "base" element without a parent. Each item will have more columns of information. I need to somehow display all the elements in the categories nested as follows:

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

I don’t know if this makes sense or not, but I hope so!

The only way I can do this is to make a query to get all the "base" elements (query for rows with parent_id = -1), and then iterate over each resulting row, asking for rows whose parent_id is equal to the current row id and then repeat the process going deeper and deeper until there are no more children for the base element.

Is there a better way?

Thanks!!

Answer 1

This is not possible in pure SQL.

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

You can represent a tree in an SQL schema, but you cannot cast a tree as you intend to do.

The only way to do this is to get a useful result by forcing as many to join as the level that you keep.

Your current scheme may support several levels, however it will be very difficult to manage more than one or two levels.

You may be interested in Nested Installation Model or Managing Hierarchical Data in MySQL

There is some implementation of a nested set, like this one for working with Doctrine 2

Answer 2

This is not possible in pure SQL, and this is one aspect of the relational model that is causing great criticism.

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

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

Answer 3

Hope I understood your question well (it's pretty late here, and I just came from the bar), if I didn’t, just correct me and I rewrote my answer.

From the above scenario, I think there is another parent table, right?

Lets imagine that attributes are id and name. The table of children is one of you (without extra 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 | +----+---------+--------+ 

To do this several times (the parent received the child who received the child, who received the child, etc.). You can accomplish this 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 did not answer what you asked, or you need further explanation, let me know;]

Hi,

Releis