Manage parent-child hierarchy in single column

Question

I have to make query to select data like parent is SFI with id 1 then remove bath , hack off and break up are SFI's child and shower tray , timber floor , screeded are break up's child.

Structure of table. (SOR table)

sor_id | items       | parent_id
-------------------------
 1 | SFI         | 0
 2 | Remove bath | 1
 3 | Hack off    | 1
 4 | break up    | 1
 5 | Shower tray | 4
 6 | Timber floor| 4
 7 | screeded    | 4
 8 | general 123 | 1

So my question is can we use self joins two times and some workout in php loops for achieveing this result?

To be frank I don't know that can we manage this hierarchy with one parent_id,

select * from sor as m_sor 
LEFT JOIN sor as c_sor ON m_sor.sor_id = c_sor.parent_id
LEFT JOIN sor as sc_sor 0N sc_sor.sor_id = c_sor.parent_id

Well, think about it, and read your query again. Very carefully. — Strawberry, Jul 14 '17 at 06:52
it was mistake in making structure here there is a `sor_id` instead of `id` I have updated my question please check my edited question. — Bhavin, Jul 14 '17 at 06:54
@Strawberry. Omg it was 0 instead of O. Now query is working but question still as that place. — Bhavin, Jul 14 '17 at 06:58
You need a recursive query really to do what you want - alas, mysql does not support recursive queries - though https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query might be of interest — Professor Abronsius, Jul 14 '17 at 06:58
@RamRaider. Can we select through this query and some work out in php because I'm confused that can we use this structure ? — Bhavin, Jul 14 '17 at 07:00
If you have a new question, either edit your question or ask a new one — Strawberry, Jul 14 '17 at 07:02
You could probably use quite a basic query in mysql and then use php to do lots of processing to work out paren/child relationships or, if you have a version of mysql that supports `Common Table Expressions` you could probably achieve the result directly in the rdbms and simply use php to output the result — Professor Abronsius, Jul 14 '17 at 07:03
@RamRaider. Ohk thank you for your reply. just read your suggested question I have only two levels so can we make two joins is it a good practice. Query like this answer's last option (repeated self joins) . https://stackoverflow.com/a/33737203/4952944 — Bhavin, Jul 14 '17 at 07:04
@RamRaider. Thank you for your precious time. I got my result. — Bhavin, Jul 14 '17 at 11:04

score 0 · Accepted Answer · answered Jul 14 '17 at 11:03

As per the @RamRaider's instruction mysql does not support recursive queries. And I can't change the table structure. So I have make a recursive function. First of all I have selected all the data from database and use the below recursive php function.

function buildTree( $ar, $pid = 0 ) {
            $op = array();
            foreach( $ar as $item ) {
                if( $item['parent_id'] == $pid ) {
                    $op[$item['sor_id']] = array(
                        'sor_id' => $item['sor_id'],
                        'item_no' => $item['item_no'],
                        'price' => $item['price'],
                        'base_qty' => $item['base_qty'],
                        'description' => $item['description'],
                        'type' => $item['type'],
                        'form_name' => $item['form_name'],
                        'status' => $item['status'],
                        'modified_date' => $item['modified_date'],
                        'parent_id' => $item['parent_id']
                    );
                    // using recursion
                    $children =  buildTree( $ar, $item['sor_id'] );
                    if( $children ) {
                        $op[$item['sor_id']]['children'] = $children;
                    }
                }
            }
            return $op;
}

So, It worked and return me the desired result.

Manage parent-child hierarchy in single column

1 Answers1