Package request and N-SubPacket Msql (same as catgeory and N sub catgeory)
I am creating a package type system. Where in my Batch table the parent package is stored ( id
which is the primary key) and its N-Sub Packet is under ( parent_id
), below is my table structure:
Packet_table
id | packet_name | parent_id |
------------------------------
1 | 01 | 0
2 | 02 | 0
3 | 03 | 1
4 | 04 | 1
5 | 05 | 1
6 | 06 | 4
7 | 07 | 4
8 | 08 | 3
9 | 09 | 5
10 | 010 | 2
........................so on and on with N packets in same table
Below is what I tried but it didn't get the correct id
N subpackage granularity :
SELECT p.`packet_name` AS MAIN, s.`packet_name` AS SUB
FROM packet_table s
LEFT JOIN packet_table p ON s.`parent_id` = p.`id`
according to the above table: id (which is main / auto increment)
id = 1 -> main package (01), its sub and N sub-packages:
01 → 03.04.05
04 → 06.07
03 → 08
05 → 09
01 -> 03 -> 08
04 -> 06 , 07
05 -> 09
its not needed above mysql code design code .. just simple N package query would be
above are just a few, but in my case for each (id) there will be N number of subpackages.
Note: It can be the same as Category and N type and also PLS note as above. I don't want the request to be described above (like a tree). I just need only mysql query in any format ..
1} just need, when I search with id = 1, then the result will give me the whole subpackage and this is N subdocuments,
2} when I search with any subpackage id then it should give me its additional package and its entire N-package, etc. etc. Also this subpackage has a main package and a dash.
UPDATE: please check below request format. I need some with N packages
Table Format 1 : thats Main Packet
+------------+------------+----------+
| Main Pkt | Sub Packet | COUNT(*) |
+------------+------------+----------+
| 01 | 03 | 1 |
| 01 | 04 | 1 |
| 01 | 05 | 1 |
--------------------------------------
Second:
Table Format 2 : thats Sub and Its N sub Packet
+------------+------------+-----------------+
| Main Pkt | Sub Packet | N Sub Packet |
+------------+------------+-----------------+
| 01 | 03 | 08 |
| 01 | 04 | 06 |
| 01 | 04 | 07 |
| 01 | 05 | 09 |
---------------------------------------------
Third:
Table Format 2 : thats Sub and Its N sub Packet
+------------+------------+-----------------+
| Main Pkt | Sub Packet | N Sub Packet |
+------------+------------+-----------------+
| 01 | 03 | 08 |
| 01 | 04 | 06 |
| 01 | 04 | 07 |
| 03 | 011 | 014 | -- *****
---------------------------------------------
above ***** : here 03 is actually sub packet of 01 hence it query will also help me
So 01 - 03 - 011 - 014
source to share
To be able to get all the children in one query, you can restructure the table to store the batch data as a nested set model .
Tree structure built from your sample data:
0
/ \
1 2
/ | \ \
3 4 5 10
| |\ \
8 6 7 9
Table:
+-------+--------------+-----+-----+
| id | packet_name | lft | rgt |
+-------+--------------+-----+-----+
| 0 | 00 | 1 | 22 |
| 1 | 01 | 2 | 17 |
| 2 | 02 | 18 | 21 |
| 3 | 03 | 3 | 6 |
| 4 | 04 | 7 | 12 |
| 5 | 05 | 13 | 16 |
| 6 | 06 | 8 | 9 |
| 7 | 07 | 10 | 11 |
| 8 | 08 | 4 | 5 |
| 9 | 09 | 14 | 15 |
| 10 | 010 | 19 | 20 |
------------------------------------
Get all nodes of a subtree rooted 01
:
SELECT node.packet_name
FROM Packet_table AS node,
Packet_table AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.packet_name = '01'
ORDER BY node.lft;
Output:
01, 03, 08, 04, 06, 07, 05, 09
Another solution is to have a loop on the PHP side and fetch all recursively (see recursive function to get all child categories ).
source to share