Query to retrieve recursively parent child nested list joining to another two tables MySQL-PHP
I've 3 tables and I want to joint them based on the following conditions. My first table is called components it has the main parent and it has a sub-component called sub_compnents which holders the component_id from the parent. The third table is called activities and this table has a parent-child relationship with itself and has a sub_component_id. parent_activity_id is expected to hold the parent activity id and is_activity is a boolean and I used to store for extra values as such to show me labels such as 1.1.1, A if it values is false to show or group related activities together by parent_activity_id, and is_activity is true then roman numbers i. ii, iii, so on will used on the loop time.
my tables design is as follow
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
-- --------------------------------------------------------
--
-- Table structure for table `tbl_component`
--
CREATE TABLE `tbl_component` (
`component_id` int NOT NULL,
`component_name` varchar(200) NOT NULL,
`component_description` text NOT NULL,
`created_time` timestamp NULL DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
--
-- Dumping data for table `tbl_component`
--
INSERT INTO `tbl_component` (`component_id`, `component_name`, `component_description`, `created_time`) VALUES
(1, 'Integrated Rangeland Development and Management (IRDM)', 'Integrated Rangeland Development and Management (IRDM) Integrated Rangeland Development and Management (IRDM)', '2021-10-05 10:29:18'),
(2, 'Livelihood Improvement & Diversification (LID) ', 'Livelihood Improvement & Diversification (LID) ', '2021-10-05 10:51:17'),
(3, 'Improving Basic Services & Capacity Building (IBSCB)', 'Improving Basic Services & Capacity Building (IBSCB)', '2021-10-05 10:51:17'),
(4, 'Project Management, Monitoring and Evaluation', 'Project Management, Monitoring and Evaluation', '2021-10-05 10:51:17');
-- --------------------------------------------------------
--
-- Table structure for table `tbl_llrp_activity`
--
CREATE TABLE `tbl_llrp_activity` (
`llrp_activity_id` int NOT NULL,
`llrp_activity_name` varchar(200) NOT NULL,
`llrp_activity_description` text,
`unit_id` int NOT NULL,
`budget` varchar(13) NOT NULL,
`male_beneficiary` int NOT NULL DEFAULT '0',
`female_beneficiary` int NOT NULL DEFAULT '0',
`youth_beneficiary` int NOT NULL DEFAULT '0',
`llrp_activity_created_time` timestamp NULL DEFAULT NULL,
`llrp_activity_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`component_id` int NOT NULL,
`sub_component_id` int NOT NULL,
`is_activity` tinyint(1) NOT NULL,
`parent_llrp_activity_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
--
-- Dumping data for table `tbl_llrp_activity`
--
INSERT INTO `tbl_llrp_activity` (`llrp_activity_id`, `llrp_activity_name`, `llrp_activity_description`, `unit_id`, `budget`, `male_beneficiary`, `female_beneficiary`, `youth_beneficiary`, `llrp_activity_created_time`, `component_id`, `sub_component_id`, `is_activity`, `parent_llrp_activity_id`) VALUES
(1, 'Consultative Capacity Building and Formulation of Project Documents', 'Consultative Capacity Building and Formulation of Project Documents', 0, '', 0, 0, 0, '2021-11-17 12:36:36', 1, 1, 0, 0),
(2, 'Training of Practitioners/Staffs, Community Consultations, Formulation, Validation, Publication and Familiarization of RMIPs', 'Training of Practitioners/Staffs, Community Consultations, Formulation, Validation, Publication and Familiarization of RMIPs', 0, '', 0, 0, 0, '2021-11-19 07:55:39', 1, 1, 0, 1),
(3, 'Training on Physical and Biological Soil and Water Conservation Activities', 'Training on Physical and Biological Soil and Water Conservation Activities', 0, '', 0, 0, 0, '2021-11-19 07:57:03', 1, 1, 0, 1),
(4, 'Refresher Trainings for TF Members and Local Facilitators ', 'Refresher Trainings for TF Members and Local Facilitators ', 3, '20000000', 200, 150, 100, '2021-11-19 09:38:11', 1, 1, 1, 2),
(5, 'Community Consultations, Training, Planning (Formulation of RMIPs) & Familiarization Activities', 'Community Consultations, Training, Planning (Formulation of RMIPs) & Familiarization Activities', 1, '', 0, 0, 0, '2022-01-21 11:09:34', 1, 1, 0, 1),
(6, 'Refresher Trainings for TF Members and Local Facilitators ', 'Refresher Trainings for TF Members and Local Facilitators ', 4, '20000000', 200, 150, 100, '2022-01-21 11:10:37', 1, 1, 0, 1),
(7, 'Formulation and Consolidation of RMIP', 'Formulation and Consolidation of RMIP', 1, '', 0, 0, 0, '2022-01-21 11:11:33', 1, 1, 0, 1),
(8, 'Formulation and Consolidation of RMIP', 'Formulation and Consolidation of RMIP', 3, '2000000', 200, 200, 100, '2022-01-21 11:12:49', 1, 1, 0, 1),
(9, 'Validation Workshop of RMIP ', 'Validation Workshop of RMIP ', 1, '3000000', 100, 2000, 100, '2022-01-21 11:13:28', 1, 1, 0, 1),
(10, 'Development of National Level RMIP Summary Document', 'Development of National Level RMIP Summary Document', 3, '10000', 100, 100, 20, '2022-01-21 11:14:18', 1, 1, 0, 1),
(11, 'Publication of RMIP Documents (Copies)', 'Publication of RMIP Documents (Copies)', 4, '2000', 100, 100, 100, '2022-01-21 11:19:14', 1, 1, 0, 1),
(12, 'Familiarization Workshop of RMIPs for Regional & Federal Stakeholders', 'Familiarization Workshop of RMIPs for Regional & Federal Stakeholders', 3, '50000', 90, 90, 50, '2022-01-21 11:20:20', 1, 1, 0, 1),
(13, 'Training on SWC Practices at Cluster Level', 'Training on SWC Practices at Cluster Level', 3, '6000', 100, 100, 20, '2022-01-21 11:22:13', 1, 1, 0, 3),
(14, 'Training on Water Spreading Weir and Gully Control Practices at Regional Level', 'Training on Water Spreading Weir and Gully Control Practices at Regional Level', 3, '30000', 100, 100, 10, '2022-01-21 11:22:59', 1, 1, 0, 3),
(15, 'Training of DAs on SWC Practices at Woreda Level', 'Training of DAs on SWC Practices at Woreda Level', 3, '25000', 250, 300, 150, '2022-01-21 11:26:08', 1, 1, 0, 3),
(16, 'Training on Rangeland Management, Forage Development and Nursery Site Establishment Practices', 'Training on Rangeland Management, Forage Development and Nursery Site Establishment Practices', 1, '', 0, 0, 0, '2022-01-21 11:33:57', 1, 1, 0, 1),
(17, 'Training on Forage Development & Management at Cluster Level ', 'Training on Forage Development & Management at Cluster Level ', 3, '233000', 200, 250, 150, '2022-01-21 11:37:06', 1, 1, 0, 16),
(18, 'Training of DAs on Forage Development & Management at Woreda Level', 'Training of DAs on Forage Development & Management at Woreda Level', 3, '100000', 100, 60, 50, '2022-01-21 11:38:21', 1, 1, 0, 16),
(19, 'Trainings on GPS, GIS and Remote Sensing Applications ', 'Trainings on GPS, GIS and Remote Sensing Applications ', 1, '', 0, 0, 0, '2022-01-21 11:39:41', 1, 1, 0, 1),
(20, 'Training on GPS, GIS and Remote Sensing Applications at Federal Level', 'Training on GPS, GIS and Remote Sensing Applications at Federal Level', 3, '60000', 10, 30, 5, '2022-01-21 11:40:27', 1, 1, 0, 19),
(21, 'Training on GPS, GIS and Remote Sensing Applications at Regional Level', 'Training on GPS, GIS and Remote Sensing Applications at Regional Level', 3, '600000', 130, 120, 60, '2022-01-21 11:41:01', 1, 1, 0, 19),
(22, 'Training on GPS, GIS and Remote Sensing Applications at Cluster Level', 'Training on GPS, GIS and Remote Sensing Applications at Cluster Level', 3, '60000', 200, 120, 30, '2022-01-21 11:41:42', 1, 1, 0, 19),
(23, 'Procurement of Handheld GPS Receivers ', 'Procurement of Handheld GPS Receivers ', 4, '560000', 120, 2000, 2000, '2022-01-21 11:42:49', 1, 1, 0, 19),
(24, 'Trainings to Capacitate Regional & Cluster Level Project Staffs & Implementing Partners', 'Trainings to Capacitate Regional & Cluster Level Project Staffs & Implementing Partners', 1, '', 0, 0, 0, '2022-01-21 11:43:42', 1, 1, 0, 1),
(25, 'Training on Strategic Investment Guidelines at Regional Level (7 Per Region & 3 Per Cluster)', 'Training on Strategic Investment Guidelines at Regional Level (7 Per Region & 3 Per Cluster)', 3, '7500000', 60, 50, 10, '2022-01-21 11:44:38', 1, 1, 0, 24),
(26, 'Training on Contract Administration and Construction Supervision at Regional Level ', 'Training on Contract Administration and Construction Supervision at Regional Level ', 3, '6000', 30, 50, 25, '2022-01-21 11:45:25', 1, 1, 0, 24),
(27, 'Training on Planning, Study and Design of Low-Traffic Volume Roads and Crossing Structures at Federal Level ', 'Training on Planning, Study and Design of Low-Traffic Volume Roads and Crossing Structures at Federal Level ', 3, '25000', 120, 60, 30, '2022-01-21 11:46:17', 1, 1, 0, 24),
(28, 'Training on Planning, Study and Design of Headwork, Water Supply and Irrigation Structures (Surface and Groundwater Sources) [Carried-over activity from 2013] at Federal Level ', 'Training on Planning, Study and Design of Headwork, Water Supply and Irrigation Structures (Surface and Groundwater Sources) [Carried-over activity from 2013] at Federal Level ', 3, '25000', 30, 20, 12, '2022-01-21 11:47:21', 1, 1, 0, 24),
(29, 'Training on 3ds Max for 3D Design, Modeling & Rendering Solution and Quantity Surveying, Rate Analysis & Cost Estimation at Federal Level ', 'Training on 3ds Max for 3D Design, Modeling & Rendering Solution and Quantity Surveying, Rate Analysis & Cost Estimation at Federal Level ', 3, '3560000', 50, 30, 10, '2022-01-21 11:47:59', 1, 1, 0, 24),
(30, 'Training on Project Management (Cost, Time & Quality Management) with the Help of Project Management Software at Federal Level ', 'Training on Project Management (Cost, Time & Quality Management) with the Help of Project Management Software at Federal Level ', 3, '356000', 20, 10, 5, '2022-01-21 11:48:33', 1, 1, 0, 24),
(31, 'Consultative Meeting & Performance Review', 'Consultative Meeting & Performance Review', 1, '', 0, 0, 0, '2022-01-21 11:48:54', 1, 1, 0, 1),
(32, 'Quarterly Consultative Meeting and Performance Review Sessions at Federal Level', 'Quarterly Consultative Meeting and Performance Review Sessions at Federal Level', 3, '25000', 20, 10, 5, '2022-01-21 11:49:56', 1, 1, 0, 31),
(33, 'Consultative Meeting with Federal & Regional Engineers on the Performance & Progress of Strategic Investments on a Quarterly Basis ', 'Consultative Meeting with Federal & Regional Engineers on the Performance & Progress of Strategic Investments on a Quarterly Basis ', 3, '3000', 30, 20, 10, '2022-01-21 11:50:42', 1, 1, 0, 31),
(34, 'Establish and Strengthen Rangeland Management Councils/Committee at Cluster and Woreda Levels', 'Establish and Strengthen Rangeland Management Councils/Committee at Cluster and Woreda Levels', 1, '', 0, 0, 0, '2022-01-21 11:51:11', 1, 1, 0, 1),
(35, 'Establishment of Rangeland Management Councils/Committees at Clusters ', 'Establishment of Rangeland Management Councils/Committees at Clusters ', 3, '300000', 30, 30, 10, '2022-01-21 11:52:20', 1, 1, 0, 34);
-- --------------------------------------------------------
--
-- Table structure for table `tbl_sub_component`
--
CREATE TABLE `tbl_sub_component` (
`sub_component_id` int NOT NULL,
`sub_component_name` varchar(200) NOT NULL,
`sub_component_description` text NOT NULL,
`sub_component_created_time` timestamp NULL DEFAULT NULL,
`sub_component_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`component_id` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
--
-- Dumping data for table `tbl_sub_component`
--
INSERT INTO `tbl_sub_component` (`sub_component_id`, `sub_component_name`, `sub_component_description`, `sub_component_created_time`, `component_id`) VALUES
(1, 'Integrated Rangeland Management Planning – IRMP', 'Integrated Rangeland Management Planning – IRMP', '2021-10-06 22:20:48', 1),
(2, 'Supporting Strategic Investments', 'Supporting Strategic Investments', '2021-10-06 22:20:48', 1),
(3, 'Conflict Management & Secure Access to Key Resources', 'Conflict Management & Secure Access to Key Resources', '2021-10-14 09:16:58', 1),
(4, 'Enhancing Pastoral and Agro-Pastoral Production System', 'Enhancing Pastoral and Agro-Pastoral Production System', '2021-10-14 09:18:19', 2),
(5, 'Promoting livelihood Diversification & Market Access', 'Promoting livelihood Diversification & Market Access', '2021-10-14 09:18:34', 2),
(6, 'Provision of Basic Social Services and Infrastructure', 'Provision of Basic Social Services and Infrastructure', '2021-10-14 09:18:57', 3),
(7, 'Institutional Capacity Building Sub Component', 'Institutional Capacity Building Sub Component', '2021-10-14 09:19:06', 3),
(8, 'Knowledge Management, Research and Policy Support sub component', 'Knowledge Management, Research and Policy Support sub component', '2021-10-14 09:19:18', 3),
(9, 'Community Contribution ', 'Community Contribution ', '2021-10-14 09:19:29', 3),
(10, 'Project Management', 'Project Management', '2021-10-14 09:19:59', 4),
(11, 'Planning, Monitoring and Evaluation', 'Planning, Monitoring and Evaluation', '2021-10-14 09:20:27', 4);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_component`
--
ALTER TABLE `tbl_component`
ADD PRIMARY KEY (`component_id`);
--
-- Indexes for table `tbl_llrp_activity`
--
ALTER TABLE `tbl_llrp_activity`
ADD PRIMARY KEY (`llrp_activity_id`);
--
-- Indexes for table `tbl_sub_component`
--
ALTER TABLE `tbl_sub_component`
ADD PRIMARY KEY (`sub_component_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_component`
--
ALTER TABLE `tbl_component`
MODIFY `component_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `tbl_llrp_activity`
--
ALTER TABLE `tbl_llrp_activity`
MODIFY `llrp_activity_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;
COMMIT;
I want to achieve for each component list with its sub-component and activity with its parent first and child next nested; some have child activities have 2/3 parents and others may have 1 or even no parent at all; as follows
hope guys, now you understand what I want to achieve. any tip or code will be welcomed.
Solution 1:
You can use a recursive cte to retrieve the activities and join them to sub-components and components. Storing the full hierarchy in your adjacency list would make things easier but I am assuming there are other dependencies within your schema.
WITH RECURSIVE activities AS
(
SELECT sub_component_id, llrp_activity_id, llrp_activity_name, 0 AS depth, CAST(llrp_activity_id AS CHAR(200)) AS path
FROM tbl_llrp_activity
WHERE parent_llrp_activity_id = 0
UNION ALL
SELECT a1.sub_component_id, a1.llrp_activity_id, a1.llrp_activity_name, a2.depth+1, CONCAT(a2.path, ',', a1.llrp_activity_id)
FROM tbl_llrp_activity a1
JOIN activities a2 ON a2.llrp_activity_id = a1.parent_llrp_activity_id
)
SELECT c.*, sc.*, a.*
FROM activities a
JOIN tbl_sub_component sc ON a.sub_component_id = sc.sub_component_id
JOIN tbl_component c ON sc.component_id = c.component_id
ORDER BY path;
Pet peeves
- Avoid calling tables table.
componentorcomponentsis definitely preferable totbl_component. - Avoid prefixing your column names with the table name.
sub_component.descriptionis easier and clearer thantbl_sub_component.sub_component_description.
UPDATE
An example using UNIONs instead of joins to get all the components, sub-components and activities. I just realised that component_id is available in the activities table so I removed the unnecessary join in the first of the three UNIONed queries. I have also added column number comments so it is clear what is being UNIONed with what. You will probably need to add where clauses to the UNIONed queries -
WITH RECURSIVE activities AS
(
SELECT
/* 1 */ component_id,
/* 2 */ sub_component_id,
/* 3 */ llrp_activity_id,
/* 4 */ llrp_activity_name,
/* 5 */ llrp_activity_description,
/* 6 */ 0 AS depth,
/* 7 */ CAST(LPAD(llrp_activity_id, 4, '0') AS CHAR(200)) AS path
FROM tbl_llrp_activity
WHERE parent_llrp_activity_id = 0
UNION ALL
SELECT
/* 1 */ a1.component_id,
/* 2 */ a1.sub_component_id,
/* 3 */ a1.llrp_activity_id,
/* 4 */ a1.llrp_activity_name,
/* 5 */ a1.llrp_activity_description,
/* 6 */ a2.depth+1,
/* 7 */ CONCAT(a2.path, ',', LPAD(a1.llrp_activity_id, 4, '0'))
FROM tbl_llrp_activity a1
JOIN activities a2 ON a2.llrp_activity_id = a1.parent_llrp_activity_id
)
(
SELECT
/* 1 */ 'activity' AS level,
/* 2 */ path,
/* 3 */ component_id,
/* 4 */ sub_component_id,
/* 5 */ llrp_activity_id,
/* 6 */ llrp_activity_name,
/* 7 */ llrp_activity_description
FROM activities
) UNION ALL (
SELECT
/* 1 */ 'component' AS level,
/* 2 */ -2 AS path,
/* 3 */ c.component_id,
/* 4 */ -1 AS sub_component_id,
/* 5 */ -1 AS llrp_activity_id,
/* 6 */ c.component_name,
/* 7 */ c.component_description
FROM tbl_component c
) UNION ALL (
SELECT
/* 1 */ 'sub-component' AS level,
/* 2 */ -1 AS path,
/* 3 */ sc.component_id,
/* 4 */ sc.sub_component_id,
/* 5 */ -1 AS llrp_activity_id,
/* 6 */ sc.sub_component_name,
/* 7 */ sc.sub_component_description
FROM tbl_sub_component sc
) ORDER BY component_id, sub_component_id, path;
Adding the "No" labelling should be done when building your HTML as this is definitely display logic. There is also no explicit ordering of components, sub-components and activities in the tables so I have assumed ordering based on ids and path.