1

I'm fetching data from mysql.

But, query is so complex.

My case is like

Table schema

CREATE TABLE `mt_department` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '[ID]',
  `mt_company_id` int(10) unsigned NOT NULL COMMENT '[companyID]',
  `reference_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '[parentLayerID]',
  `name` varchar(100) NOT NULL COMMENT '[name]',
  `layer` enum('1','2','3','4') NOT NULL DEFAULT '1' COMMENT '[layer]',
  `registered_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_mt_department_mt_company_idx` (`mt_company_id`),
  CONSTRAINT `fk_mt_department_mt_company` FOREIGN KEY (`mt_company_id`) REFERENCES `mt_company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB

Row list

+----+---------------+--------------+----------+-------+---------------------+---------------------+------------+
| id | mt_company_id | reference_id | name     | layer | registered_at       | updated_at          | deleted_at |
+----+---------------+--------------+----------+-------+---------------------+---------------------+------------+
|  1 |             6 |            0 | Layer1-1 | 1     | 2018-07-27 14:45:49 | 0000-00-00 00:00:00 | NULL       |
|  2 |             6 |            0 | Layer1-2 | 1     | 2018-07-27 14:45:52 | 0000-00-00 00:00:00 | NULL       |
|  3 |             6 |            0 | Layer1-3 | 1     | 2018-07-27 14:45:55 | 0000-00-00 00:00:00 | NULL       |
|  4 |             6 |            0 | Layer1-4 | 1     | 2018-07-27 14:45:57 | 0000-00-00 00:00:00 | NULL       |
|  5 |             6 |            0 | Layer1-5 | 1     | 2018-07-27 14:46:05 | 0000-00-00 00:00:00 | NULL       |
|  6 |             6 |            3 | Layer2-1 | 2     | 2018-07-27 14:46:21 | 0000-00-00 00:00:00 | NULL       |
|  7 |             6 |            3 | Layer2-2 | 2     | 2018-07-27 14:46:26 | 0000-00-00 00:00:00 | NULL       |
|  8 |             6 |            1 | Layer2-1 | 2     | 2018-07-27 14:46:49 | 0000-00-00 00:00:00 | NULL       |
|  9 |             6 |            0 | Layer1-6 | 1     | 2018-07-27 14:47:01 | 0000-00-00 00:00:00 | NULL       |
| 10 |             6 |            7 | Layer3-1 | 3     | 2018-07-27 14:47:15 | 0000-00-00 00:00:00 | NULL       |
| 11 |             6 |           10 | Layer4-1 | 4     | 2018-07-27 14:47:27 | 0000-00-00 00:00:00 | NULL       |
+----+---------------+--------------+----------+-------+---------------------+---------------------+------------+

Let me explain data structure!

Layer can have 1 ~ 4. reference_id is 0 for layer 1.

reference_id of layer 2 row must has id of specific layer 1.

So, Now i want to fetch result like this

+----+---------------+--------------+----------+-------+---------------------+---------------------+------------+
| id | mt_company_id | reference_id | name     | layer | registered_at       | updated_at          | deleted_at |
+----+---------------+--------------+----------+-------+---------------------+---------------------+------------+
|  1 |             6 |            0 | Layer1-1 | 1     | 2018-07-27 14:45:49 | 0000-00-00 00:00:00 | NULL       |
|  8 |             6 |            1 | Layer2-1 | 2     | 2018-07-27 14:46:49 | 0000-00-00 00:00:00 | NULL       |
|  2 |             6 |            0 | Layer1-2 | 1     | 2018-07-27 14:45:52 | 0000-00-00 00:00:00 | NULL       |
|  3 |             6 |            0 | Layer1-3 | 1     | 2018-07-27 14:45:55 | 0000-00-00 00:00:00 | NULL       |
|  6 |             6 |            3 | Layer2-1 | 2     | 2018-07-27 14:46:21 | 0000-00-00 00:00:00 | NULL       |
|  7 |             6 |            3 | Layer2-2 | 2     | 2018-07-27 14:46:26 | 0000-00-00 00:00:00 | NULL       |
| 10 |             6 |            7 | Layer3-1 | 3     | 2018-07-27 14:47:15 | 0000-00-00 00:00:00 | NULL       |
| 11 |             6 |           10 | Layer4-1 | 4     | 2018-07-27 14:47:27 | 0000-00-00 00:00:00 | NULL       |
|  4 |             6 |            0 | Layer1-4 | 1     | 2018-07-27 14:45:57 | 0000-00-00 00:00:00 | NULL       |
|  5 |             6 |            0 | Layer1-5 | 1     | 2018-07-27 14:46:05 | 0000-00-00 00:00:00 | NULL       |
|  9 |             6 |            0 | Layer1-6 | 1     | 2018-07-27 14:47:01 | 0000-00-00 00:00:00 | NULL       |
+----+---------------+--------------+----------+-------+---------------------+---------------------+------------+

If you have other idea Please tell me.

Thank you

  • It seems you need to sort data only, is it? If so, remove all fields from example data which do not depends on the result, and thoroughly explain why records are sorted in this order. Now it looks like a tree structure sorting, is it? – Akina Jul 27 '18 at 9:22
  • @Akina No. Not sort data only. Layer range is 1~4. 1 is parent. 2 is first child. 3 is second child. 4 is third child. parent can have many 2 child. 2 child can have many 3 child. – jonggu Jul 27 '18 at 10:02
  • 1 is parent I see, reference_id = 0 shows it. 2 is first child. 3 is second child. 4 is third child. parent can have many 2 child. 2 child can have many 3 child. And? It's a common tree structure, the only difference is that many trees (each starting with its own Layer-1 node) are stored in one table. As I see you output the firts tree (in scanning by depth order), then second one and so on... If not - explain thoroughly. – Akina Jul 27 '18 at 10:09
  • @Akina Sorry. My english is not good. Now Your understanding is same my case! – jonggu Jul 27 '18 at 10:19
  • 1
    The sorting you need is the only task which processes the data as an hierarchical one? if so, additional fully qualified pathname (materialized path) field is enough (for 4 levels and, for example, 8-digit max ID it is to be VARCHAR(32)). But if the nodes may move from one tree to another (with their childs) or some another operations must be performed it's better to think for a special structure (nested set, for example). – Akina Jul 27 '18 at 11:21

Your Answer

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

Browse other questions tagged or ask your own question.