Recursive CTEで多階層カテゴリのレコードを取得

MySQL8.0から Recursive CTE(Common Table Expressions) を利用できるようになりました。ここでは、再帰クエリ の動作確認をします。多階層カテゴリを持つデータ構造から、特定カテゴリの子孫カテゴリをまとめて取得します。

前準備

検証用テーブル作成

下記クエリを実行してテーブルを作成します。

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

検証用レコード挿入

下記カテゴリ構造となるようにレコードを挿入します。

├── 暮らし
│   ├── 料理
│   │   ├── 和食
│   │   │   ├── 肉じゃが
│   │   │   ├── 味噌汁
│   │   │   └── 天ぷら
│   │   ├── 中華
│   │   ├── イタリアン
│   │   └── フレンチ
│   ├── ペット
│   └── ファッション
└── ビジネス
    ├── 経済学
    ├── マーケティング
    └── 経営

下記クエリを実行します。

INSERT INTO `categories`
(`id`, `name`, `parent_id`)
VALUES
(1, "暮らし", NULL),
(2, "料理", 1),
(3, "和食", 2),
(4, "肉じゃか", 3),
(5, "味噌汁", 3),
(6, "天ぷら", 3),
(7, "中華", 2),
(8, "イタリアン", 2),
(9, "フレンチ", 2),
(10, "ペット", 1),
(11, "ファッション", 1),
(12, "ビジネス", NULL),
(13, "経済学", 12),
(14, "マーケティング", 12),
(15, "経営", 12);
mysql> SELECT * FROM `categories`;
+----+-----------------------+-----------+
| id | name                  | parent_id |
+----+-----------------------+-----------+
|  1 | 暮らし                |      NULL |
|  2 | 料理                  |         1 |
|  3 | 和食                  |         2 |
|  4 | 肉じゃか              |         3 |
|  5 | 味噌汁                |         3 |
|  6 | 天ぷら                |         3 |
|  7 | 中華                  |         2 |
|  8 | イタリアン            |         2 |
|  9 | フレンチ              |         2 |
| 10 | ペット                |         1 |
| 11 | ファッション          |         1 |
| 12 | ビジネス              |      NULL |
| 13 | 経済学                |        12 |
| 14 | マーケティング        |        12 |
| 15 | 経営                  |        12 |
+----+-----------------------+-----------+
15 rows in set (0.00 sec)

再帰クエリで子孫をまとめて取得

Recursive CTEの書き方

再帰クエリは以下のように作成します。

WITH RECURSIVE cte AS (
  SELECT ...      -- 最初のクエリ
UNION ALL
  SELECT ...      -- ここの部分が再帰クエリになります
)
SELECT * FROM cte;

例を示します。

WITH RECURSIVE `cte` AS (
  SELECT 1 as `id`
UNION ALL
  SELECT `cte`.`id` + 1 as `id`
  FROM `cte`
  WHERE `cte`.`id` < 5
)
SELECT * FROM `cte`;
mysql> WITH RECURSIVE `cte` AS (
    ->   SELECT 1 as `id`
    -> UNION ALL
    ->   SELECT `cte`.`id` + 1 as `id`
    ->   FROM `cte`
    ->   WHERE `cte`.`id` < 5
    -> )
    -> SELECT * FROM `cte`;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

子孫カテゴリを取得

暮らしカテゴリ自身その子孫カテゴリ を取得するクエリです。

SET @root_name = '暮らし';

WITH RECURSIVE `cte` AS (
  SELECT `id`, `name`, `parent_id` 
  FROM `categories` 
  WHERE `name` = @root_name
UNION ALL
  SELECT `child_categories`.`id`, 
         `child_categories`.`name`,
         `child_categories`.`parent_id` 
  FROM `categories` AS `child_categories`, `cte`
  WHERE `cte`.`id` = `child_categories`.`parent_id`
)
SELECT * FROM `cte`;

実行します。

mysql> SET @root_name = '暮らし';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> WITH RECURSIVE `cte` AS (
    ->   SELECT `id`, `name`, `parent_id` 
    ->   FROM `categories` 
    ->   WHERE `name` = @root_name
    -> UNION ALL
    ->   SELECT `child_categories`.`id`, 
    ->          `child_categories`.`name`,
    ->          `child_categories`.`parent_id` 
    ->   FROM `categories` AS `child_categories`, `cte`
    ->   WHERE `cte`.`id` = `child_categories`.`parent_id`
    -> )
    -> SELECT * FROM `cte`;
+------+--------------------+-----------+
| id   | name               | parent_id |
+------+--------------------+-----------+
|    1 | 暮らし             |      NULL |
|    2 | 料理               |         1 |
|   10 | ペット             |         1 |
|   11 | ファッション       |         1 |
|    3 | 和食               |         2 |
|    7 | 中華               |         2 |
|    8 | イタリアン         |         2 |
|    9 | フレンチ           |         2 |
|    4 | 肉じゃか           |         3 |
|    5 | 味噌汁             |         3 |
|    6 | 天ぷら             |         3 |
+------+--------------------+-----------+
11 rows in set (0.01 sec)

今度は、料理カテゴリ自身その子孫カテゴリ を取得します。

mysql> SET @root_name = '料理';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> WITH RECURSIVE `cte` AS (
    ->   SELECT `id`, `name`, `parent_id` 
    ->   FROM `categories` 
    ->   WHERE `name` = @root_name
    -> UNION ALL
    ->   SELECT `child_categories`.`id`, 
    ->          `child_categories`.`name`,
    ->          `child_categories`.`parent_id` 
    ->   FROM `categories` AS `child_categories`, `cte`
    ->   WHERE `cte`.`id` = `child_categories`.`parent_id`
    -> )
    -> SELECT * FROM `cte`;
+------+-----------------+-----------+
| id   | name            | parent_id |
+------+-----------------+-----------+
|    2 | 料理            |         1 |
|    3 | 和食            |         2 |
|    7 | 中華            |         2 |
|    8 | イタリアン      |         2 |
|    9 | フレンチ        |         2 |
|    4 | 肉じゃか        |         3 |
|    5 | 味噌汁          |         3 |
|    6 | 天ぷら          |         3 |
+------+-----------------+-----------+
8 rows in set (0.00 sec)

参考

【エンジニア向け】仕事を見つける方法

転職する

転職エージェントを活用する

転職サイトの場合、自身でサイト上から企業を探す必要があります。 一方「レバテックキャリア」 などの転職エージェントの場合、エージェントが企業を紹介してくれます。エージェントが間に入ることにより、日程調整や、条件交渉などもサポートしてくれます。

転職ドラフトを活用する

転職ドラフト」は、 企業がITエンジニアをドラフトという形で指名するサービスです。年収が最初に提示されるなどのメリットがあります。 ただ、初回登録時にレジュメ作成が必要で、すでにエンジニア経験が豊富にあるエンジニア向けのサービスかと思います。 レジュメ作成が手間ですが、自身のキャリアを見直す機会になり、他の仕事探しにも役立つはずです。

エンジニア転職保証のあるスクールを活用する

ある程度、開発経験のあるかたであれば、独学で必要なスキルを身につけることができるはずです。ただ、別業種からエンジニアに転職したい場合など、1から独学で学ぶのはハードルが高いです。そういった方は、スクールの活用を検討しても良いと思います。 「TechAcademy」は、エンジニア転職保証コースを提供しています。給付金制度の対象講座として認定されているため、金銭面の負担も抑えることができます。

フリーランスとして活動する

レバテックフリーランス」「ITプロパートナーズ」「ギークスジョブ」は、フリーランスエージェントサービスです。 エージェントによって、支払いサイトなど細かい違いはありますが、まずは良い案件を見つけることが重要です。 登録自体は無料なので、複数エージェントに登録して、より多くの案件を紹介してもらうのがおすすめです。

logo
わくわくBank.
技術系の記事を中心に、役に立つと思ったこと、整理したい情報などを掲載しています。