MySQL,MariaDBでタグ機能（TAG)を実現する(TOXI法）③タグマップデータ投入

前回

hit.hateblo.jp

にて、タグデータを投入する説明はしましたが、肝心のDIARYの中での紐付けについては書いていませんでした。 前回の記事を含め、タグのマッピングの登録方法についても記載していきます。もちろんストアドプロシージャベースでの話です

tagmapsの構成

• diary_id は、 diary テーブルのidを指し示しています
• tag_id は、　tagsテーブルのidを指し示しております

tagmapsは、diaryからtagsへの橋渡しをする役目をするって感じですね。

ストアドプロシージャ

基本的には、前回、tagsテーブルのストアドプロシージャと同じですね。

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_add_tag_maps $$


CREATE PROCEDURE `sp_add_tag_maps`(
IN _diary_id int(11),
IN _tag_id int(11),
OUT _tag_map_id int(11),
OUT _result int(11)
) 
BEGIN

   DECLARE _not_found tinyint UNSIGNED DEFAULT 0;

   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
     SET _result = -99;
   END;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;

   SET _result=0 ;

   SELECT id INTO _tag_map_id FROM tag_maps 
   WHERE
     diary_id = _diary_id AND 
     tag_id = _tag_id
   ;

   IF _not_found = 1 THEN
     INSERT INTO
         tag_maps
       (diary_id,tag_id)
     VALUES
       (_diary_id,_tag_id)
     ;

     SELECT LAST_INSERT_ID() INTO _tag_map_id ;
   END IF ;     

END $$

DELIMITER ;

e.g.

前回と違うのは、tagsテーブルへの挿入と、tag_mapsテーブルへの挿入は、同じタイミングでやったほうがいいといったところでしょうか？

きっとこんなパターンで登録します

1. diaries に登録するストアド→ diary_idを取得
2. tags に登録されるストアド → tag_idを取得
3. 上記のidをtag_mapsに格納する

今回 1.については、特に説明はないので割愛します。1.が終わったものとして説明します。

• diary_idは、1です

 SELECT * FROM diaries ;
+----+---------------------+---------------------+---------+--------+--------------+
| id | created_at          | updated_at          | user_id | title  | detail       |
+----+---------------------+---------------------+---------+--------+--------------+
|  1 | 2016-10-28 17:29:50 | 2016-10-28 17:29:50 |    1000 | 日記   | 今日は雨     |
+----+---------------------+---------------------+---------+--------+--------------+

SET @diary_id=1 ; 

• tagsの登録 / tag_mapsの登録

SET @name='weather' ; CALL sp_add_tags(@name,@tag_id,@result) ; SELECT @name,@tag_id,@result ;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.04 sec)

+---------+---------+---------+
| @name   | @tag_id | @result |
+---------+---------+---------+
| weather |      14 |       0 |
+---------+---------+---------+
1 row in set (0.00 sec)

-- 上記で取得した、@tag_id 変数をそのまま、引数として持ち回る

 CALL sp_add_tag_maps(@diary_id,@tag_id,@tag_map_id,@result) ; SELECT @diary_id,@tag_id,@tag_map_id,@result ;
Query OK, 1 row affected (0.00 sec)

+-----------+---------+-------------+---------+
| @diary_id | @tag_id | @tag_map_id | @result |
+-----------+---------+-------------+---------+
|         1 |      14 |           1 |       0 |
+-----------+---------+-------------+---------+
1 row in set (0.00 sec)

-- タグが複数ある場合、sp_add_tagsとsp_add_tag_mapsを繰り返し実施する、以下では、weather の他、rainおよび"天気"を登録している例です。diary_idは変わらないので使いまわしてます

SET @name='rain' ; CALL sp_add_tags(@name,@tag_id,@result) ;
CALL sp_add_tag_maps(@diary_id,@tag_id,@tag_map_id,@result) ; SELECT @diary_id,@tag_id,@tag_map_id,@result ;

SET @name='天気' ; CALL sp_add_tags(@name,@tag_id,@result) ;
CALL sp_add_tag_maps(@diary_id,@tag_id,@tag_map_id,@result) ; SELECT @diary_id,@tag_id,@tag_map_id,@result ;

このままの流れで進むと、３件、tag_mapsが登録されているはずです

SELECT * FROM tag_maps ;
+----+---------------------+---------------------+---------+----------+--------+
| id | created_at          | updated_at          | user_id | diary_id | tag_id |
+----+---------------------+---------------------+---------+----------+--------+
|  1 | 2016-10-28 17:40:28 | 2016-10-28 17:40:28 |    NULL |        1 |     14 |
|  2 | 2016-10-28 17:44:46 | 2016-10-28 17:44:46 |    NULL |        1 |     15 |
|  3 | 2016-10-28 17:44:49 | 2016-10-28 17:44:49 |    NULL |        1 |     16 |
+----+---------------------+---------------------+---------+----------+--------+

前回のSQLを実行する

前回のSQLを実行してみましょう

SELECT
  d.id AS diary_id,
  d.title AS title ,
  d.detail AS detail,
  GROUP_CONCAT(tag.name SEPARATOR ',') AS tag
FROM
  diaries d
INNER JOIN
  tag_maps map
ON
  d.id = map.diary_id
INNER JOIN
  tags tag
ON
  map.tag_id = tag.id
GROUP BY
  d.id \G

結果はどのようになっているでしょうか？

*************************** 1. row ***************************
diary_id: 1
   title: 日記
  detail: 今日は雨
     tag: weather,rain,天気
1 row in set (0.00 sec)

ど、どうでしょうか？ばっちりじゃないっすか？？？

次回はこのテーブル群でどのような抽出をできるかを見てみたいと思います

hit.hateblo.jp