Mysql – How make recursive json on thesql

hierarchyjsonMySQLrecursive

my table looks like

CREATE TABLE `test_comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `test_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `comment` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `rate` mediumint(9) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_comments_user_id_foreign` (`user_id`),
  KEY `test_comments_test_id_foreign` (`test_id`),
  KEY `test_comments_parent_id_foreign` (`parent_id`),
  CONSTRAINT `test_comments_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `test_comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `test_comments_test_id_foreign` FOREIGN KEY (`test_id`) REFERENCES `tests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `test_comments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

how can i retrieve comments like this structure:

[
    {
        "id": 3,
        "comment": "...",
        "comments": [
            {
                "id": 12,
                "comment": "answer for #3",
                "comments": []
            },
            {
                "id": 14,
                "comment": "answer for #3",
                "comments": [
                    {
                        "id": 22,
                        "comment": "sub comment for #14",
                        "comments": [
                            {
                                "id": 23,
                                "comment": "discussion continues..."
                            }
                        ]
                    }
                ]
            },
        ]
    },
    {
        "id": 44,
        "comment": "wow so many comments!",
        "comments": []
    }
]

I had an idea to form this structure in the application layer, but I think that this will negatively affect the memory consumption and the load as a whole.

I do not know how others solve such problems and i have not yet found a solution. I will be grateful if the community will tell me the right way 🙂

Best Answer

MySQL 8+

Using MySQL 8, you can write a Recursive Common Table Expression.

The example under Hierarchical Data Traversal is pretty much what you're looking for, what you call parent_id they call manager_id

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
  FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  -- Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      -- John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT *
FROM employee_paths
ORDER BY path;

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

The docs even explain it,

How the CTE works:

  • The nonrecursive SELECT produces the row for the CEO (the row with a NULL manager ID).

  • The path column is widened to CHAR(200) to ensure that there is room for the longer path values produced by the recursive SELECT.

  • Each row produced by the recursive SELECT finds all employees who report directly to an employee produced by a previous row. For each such employee, the row includes the employee ID and name, and the employee management chain. The chain is the manager's chain, with the employee ID added to the end.

  • Recursion ends when employees have no others who report to them.

They further have an example wuth specific employee or employees, for more information see "Hierarchical Data Traversal" with MySQL 8

Related Question