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

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 :)

  • What version of MySQL are you using? – Evan Carroll Jul 22 '18 at 15:05

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 (
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
  FOREIGN KEY (manager_id) REFERENCES employees (id)
(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
  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
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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.