'In MySQL, how can I create a recursive query for a non-binary tree structure?
Good morning, I have two tables according the following scheme
taxonomy
| id | parent_id |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | 1 |
| 4 | 1 |
| 5 | 4 |
| 6 | 5 |
translation
| language_code | taxonomy_id | field_name | value |
|---|---|---|---|
| it | 1 | "name" | "name1" |
| it | 1 | "code" | "code1" |
| it | 2 | "name" | "name2" |
| it | 2 | "code" | "code2" |
| it | 3 | "name" | "name3" |
| it | 3 | "code" | "code3" |
| it | 4 | "name" | "name4" |
| it | 4 | "code" | "code4" |
| it | 5 | "name" | "name5" |
| it | 5 | "code" | "code5" |
| it | 6 | "name" | "name6" |
| it | 6 | "code" | "code6" |
I need to get a resultset like this:
[
{
"id": 1,
"parent_id": null,
"fields": [
{
"field": "name",
"value": "name1"
},
{
"field": "code",
"value": "code1"
}
]
"childs": [
{
"id": 3,
"parent_id": 1,
"fields": [
{
"field": "name",
"value": "name3"
},
{
"field": "code",
"value": "code3"
}
]
},
{
"id": 4,
"parent_id": 1,
"fields": [
{
"field": "name",
"value": "name4"
},
{
"field": "code",
"value": "code4"
}
],
"childs": [
{
"id": 5,
"parent_id": 4,
"fields": [
{
"field": "name",
"value": "name5"
},
{
"field": "code",
"value": "code5"
}
],
"childs": [
{
"id": 6,
"parent_id": 5,
"fields": [
{
"field": "name",
"value": "name6"
},
{
"field": "code",
"value": "code6"
}
]
}
]
}
]
}
]
},
{
"id": 2,
"parent_id": null,
"fields": [
{
"field": "name",
"value": "name2"
},
{
"field": "code",
"value": "code2"
}
]
}
]
Is it possible to write a MySQL 8 query, it was better if recursive, that allows me to have the same result?
Thanks for support
MySQL Scripts (as generated in MySQL Workbench)
-- MySQL dump 10.13 Distrib 8.0.27, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: mydb
-- ------------------------------------------------------
-- Server version 8.0.27
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `taxonomy`
--
DROP TABLE IF EXISTS `taxonomy`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `taxonomy` (
`id` smallint unsigned NOT NULL AUTO_INCREMENT,
`parent_id` smallint unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_taxonomy_taxonomy1_idx` (`parent_id`),
CONSTRAINT `fk_taxonomy_taxonomy1` FOREIGN KEY (`parent_id`) REFERENCES `taxonomy` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `taxonomy`
--
LOCK TABLES `taxonomy` WRITE;
/*!40000 ALTER TABLE `taxonomy` DISABLE KEYS */;
INSERT INTO `taxonomy` VALUES (1,NULL),(2,NULL),(3,1),(4,1),(5,4),(6,5);
/*!40000 ALTER TABLE `taxonomy` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `translation`
--
DROP TABLE IF EXISTS `translation`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `translation` (
`language_code` varchar(2) NOT NULL,
`taxonomy_id` smallint unsigned DEFAULT NULL,
`field_name` varchar(20) NOT NULL,
`value` text NOT NULL,
UNIQUE KEY `translation_UNIQUE` (`language_code`,`taxonomy_id`,`field_name`),
KEY `fk_translation_taxonomy1_idx` (`taxonomy_id`),
CONSTRAINT `fk_translation_language1` FOREIGN KEY (`language_code`) REFERENCES `language` (`code`) ON DELETE CASCADE,
CONSTRAINT `fk_translation_taxonomy1` FOREIGN KEY (`taxonomy_id`) REFERENCES `taxonomy` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `translation`
--
LOCK TABLES `translation` WRITE;
/*!40000 ALTER TABLE `translation` DISABLE KEYS */;
INSERT INTO `translation` VALUES ('it',NULL,NULL,1,'name','name1'),('it',NULL,NULL,1,'code','code1'),('it',NULL,NULL,2,'name','name2'),('it',NULL,NULL,2,'code','code2'),('it',NULL,NULL,3,'name','name3'),('it',NULL,NULL,3,'code','code3'),('it',NULL,NULL,4,'name','name4'),('it',NULL,NULL,4,'code','code4'),('it',NULL,NULL,5,'name','name5'),('it',NULL,NULL,5,'code','code5'),('it',NULL,NULL,6,'name','name6'),('it',NULL,NULL,6,'code','code6');
/*!40000 ALTER TABLE `translation` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-05-10 9:17:44
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|