fresher fresher - 2 months ago 29
MySQL Question

Tree-Hierarchical query without stored procedure

I am using MySQL, is it possible to get following result in single SQL statement from below table structure?

Currently I am able to get same result by using logical while loop in my PHP code. It will be good if I can achieve in a single SQL, for performance.

Expected result:

|----------+-----------------------------------------------+
| Id (PK) + headerANDsubheader +
|----------+-----------------------------------------------+
| 1 + A-head +
| 4 + -A-head-A1-subHead +
| 5 + -A-head-A2-subHead +
| 6 + --A-head-A1-subHead-A1.1-subHead +
| 7 + --A-head-A1-subHead-A1.2-subHead +


Column
Id
is primary key. If parent key is 0 then it means, its root level heading.

If
ParentKey
is not equal to 0 then it means it a sub heading of someone, and
ParentKey
is a pointer for that.

Table: Header_sub

|----------+-----------------------------------------------+------------+
| Id (PK) + headerANDsubheader + ParentKey +
|----------+-----------------------------------------------+------------+
| 1 + A-head + 0 +
|----------+-----------------------------------------------+------------+
| 2 + B-head + 0 +
|----------+-----------------------------------------------+------------+
| 3 + C-head + 0 +
|----------+-----------------------------------------------+------------+
| 4 + A-head-A1-subHead + 1 +
|----------+-----------------------------------------------+------------+
| 5 + A-head-A2-subHead + 1 +
|----------+-----------------------------------------------+------------+
| 6 + A-head-A1-subHead-A1.1-subHead + 4 +
|----------+-----------------------------------------------+------------+
| 7 + A-head-A1-subHead-A1.2-subHead + 4 +
|----------+-----------------------------------------------+------------+


I am trying like this ...

SELECT
CONCAT(REPEAT(' ', (COUNT(parent.subject_group_name) - 1) ), node.subject_group_name) AS name
FROM
major_scholastic as node,
major_scholastic as parent
WHERE
node.s_gid BETWEEN parent.s_gid AND parent.parent_id
GROUP BY node.subject_group_name
ORDER BY node.s_gid

Answer

Your headerANDsubheader column is some kind of meterialized path. That allows you to get all ancestors (not only the direct parent) by using a JOIN with a LIKE condition.

The following query demonstrates how to get the information you might need for different tasks:

select node.*
    , group_concat(anc.Id order by char_length(anc.headerANDsubheader)) as idPath
    , group_concat(lpad(anc.Id, 10, 0) order by char_length(anc.headerANDsubheader)) as idPathSortable
    , count(anc.Id) as depthLevel
    , concat(repeat('- ', count(anc.Id)-1), node.headerANDsubheader) as indendtedHeader
from header_sub node
join header_sub anc
    on node.headerANDsubheader like concat(anc.headerANDsubheader, '%')
group by node.Id
order by idPathSortable

The result looks like this:

| Id |             headerANDsubheader | ParentKey | idPath |                   idPathSortable | depthLevel |                    indendtedHeader |
|----|--------------------------------|-----------|--------|----------------------------------|------------|------------------------------------|
|  1 | A-head                         |         0 | 1      | 0000000001                       |          1 | A-head                             |
|  4 | A-head-A1-subHead              |         1 | 1,4    | 0000000001,0000000004            |          2 | - A-head-A1-subHead                |
|  6 | A-head-A1-subHead-A1.1-subHead |         4 | 1,4,6  | 0000000001,0000000004,0000000006 |          3 | - - A-head-A1-subHead-A1.1-subHead |
|  7 | A-head-A1-subHead-A1.2-subHead |         4 | 1,4,7  | 0000000001,0000000004,0000000007 |          3 | - - A-head-A1-subHead-A1.2-subHead |
|  5 | A-head-A2-subHead              |         1 | 1,5    | 0000000001,0000000005            |          2 | - A-head-A2-subHead                |
|  2 | B-head                         |         0 | 2      | 0000000002                       |          1 | B-head                             |
|  3 | C-head                         |         0 | 3      | 0000000003                       |          1 | C-head                             |

sqlfiddle

Now having that it's a small step to get the result you need:

select node.Id, concat(
    repeat(' ', count(anc.Id)-1),
    repeat('-', count(anc.Id)-1),
    node.headerANDsubheader
) as indendtedHeader
from header_sub node
join header_sub anc
    on node.headerANDsubheader like concat(anc.headerANDsubheader, '%')
group by node.Id
order by group_concat(lpad(anc.Id, 10, 0) order by char_length(anc.headerANDsubheader))

Result:

| Id |                    indendtedHeader |
|----|------------------------------------|
|  1 | A-head                             |
|  4 |  -A-head-A1-subHead                |
|  6 |   --A-head-A1-subHead-A1.1-subHead |
|  7 |   --A-head-A1-subHead-A1.2-subHead |
|  5 |  -A-head-A2-subHead                |
|  2 | B-head                             |
|  3 | C-head                             |

sqlfiddle

Update:

join header_sub anc
    on node.headerANDsubheader like concat(anc.headerANDsubheader, '%')

anc is a shortcut for "ancestor". We want to join every node with all its ancestors (including itself). The condition can be read as anc.headerANDsubheader IS PREFIX OF node.headerANDsubheader. So "A-head-A1-subHead-A1.1-subHead" will be joined with "A-head", "A-head-A1-subHead" and "A-head-A1-subHead-A1.1-subHead". Grouping the result by node.id we can use the aggregations COUNT to get the depth level and GROUP_CONCAT to generate a usefull path. However it would be better to store the depth and path in the table, so we wouldn't need the join at all.

Comments