MySQLで1対多というテーブル構造でデータ結合と条件検索する方法

MySQLで飲食店舗テーブルと休業日テーブルがあり、1店舗に対して複数行の休業日データが入っているというケースがあった。1対多のデータを結合しつつ条件検索やソートを行いたい。以下に具体例と対応方法をメモ。

具体例

飲食店舗テーブルは前回の記事と同様で新たに休業日テーブルというのを追加した。具体的には以下のような形となる。

mysql> show columns from shop;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11)          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)     | NO   |     | NULL    |                |
| start_time | time             | YES  |     | NULL    |                |
| end_time   | time             | YES  |     | NULL    |                |
| last_order | int(11) unsigned | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> select * from shop;
+----+-----------+------------+----------+------------+
| id | name      | start_time | end_time | last_order |
+----+-----------+------------+----------+------------+
|  1 | 牛角      | 10:00:00   | 22:00:00 |         30 |
|  2 | 叙々苑    | 09:00:00   | 19:00:00 |         15 |
+----+-----------+------------+----------+------------+

mysql> show columns from holiday;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| shop_id | int(10) unsigned | NO   |     | NULL    |                |
| holiday | date             | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

mysql> select * from holiday;
+----+---------+------------+
| id | shop_id | holiday    |
+----+---------+------------+
|  1 |       1 | 2020-09-12 |
|  2 |       1 | 2020-09-20 |
+----+---------+------------+

shopテーブルのidとholidayテーブルのshop_idが対になっている。

対応方法

//MySQL5.1の場合
SELECT 
    *, 
    SEC_TO_TIME (
        TIME_TO_SEC(s.end_time) - ( s.last_order * 60)
    ) AS last_time,
    IF(
        start_time <= now() &&
        SEC_TO_TIME (
            TIME_TO_SEC(s.end_time) - ( s.last_order * 60 )
        ) >= now() && 
        (
            GROUP_CONCAT(h.holiday) NOT LIKE "%2020-09-12%" || GROUP_CONCAT(h.holiday) IS NULL
        ), 1, 0
    ) AS open_flg,
    GROUP_CONCAT(h.holiday) AS holiday_list, 
    IF( GROUP_CONCAT(h.holiday) LIKE "%2020-09-12%", 1, 0) AS holiday_flg 
FROM shop AS s 
LEFT JOIN holiday AS h 
ON s.id = h.shop_id
GROUP BY s.id;

//MySQL5.7の場合
SELECT 
    *, 
    ( end_time - INTERVAL last_order MINUTE ) AS last_time, 
    IF(
        start_time <= now() && 
        ( end_time - INTERVAL last_order MINUTE ) >= now() && 
        (
            GROUP_CONCAT(h.holiday) NOT LIKE "%2020-09-12%" || GROUP_CONCAT(h.holiday) IS NULL
        ), 1, 0
    ) AS open_flg,
    GROUP_CONCAT(h.holiday) AS holiday_list, 
    IF( GROUP_CONCAT(h.holiday) LIKE "%2020-09-12%", 1, 0) AS holiday_flg 
FROM shop AS s 
LEFT JOIN holiday AS h 
ON s.id = h.shop_id
GROUP BY s.id;

「2020-09-12」の部分は適宜置き換える。PHPの場合だとdate('Y-m-d')とかになる。

1対多の結合

1対多の結合だがLEFT JOINしつつGROUP_CONCAT(h.holiday)とGROUP BY s.idが必要になる。LEFT JOINだけだと同一店舗に複数休業日を登録していた場合、登録数分表示されてしまう。

LEFT JOINとGROUP_CONCATの2つだと休業日が登録されていない店舗は表示されない。

LEFT JOINしつつGROUP_CONCAT(h.holiday)とGROUP BY s.idを指定することで複数休業日を登録していても1列で表示される上、休業日が未登録の店舗も表示される。

open_flgの部分

長めのif文のところだが「営業時間内(ラストオーダー時間の減算含む)」という判別と「休業日ではない」もしくは「休業日自体が登録されていない」のいずれかの場合にフラグを立てている。フラグが立っている場合は営業中、立っていない場合は営業時間外もしくは休業日という判定。

「営業時間内(ラストオーダー時間の減算含む)」については前回の記事を参考。

「休業日ではない」は「GROUP_CONCAT(h.holiday) NOT LIKE "%2020-09-12%"」の部分になる。休業日自体は登録しているものの、検索日(2020-09-12)を含まない場合はtrue判定。

上記だけだと休業日未登録店舗のNULLの関係で望んだ挙動にならないので「休業日自体が登録されていない = 休業日ではない」という判定を「GROUP_CONCAT(h.holiday) IS NULL」部分で行っている。

holiday_flgの部分

営業時間云々に関わらず休業日に検索日(2020-09-12)が含まれている場合はフラグを立てている。open_flgがあればいらないんじゃないかと思ったが、open_flgだけだと休業日なのか営業時間外なのか分からない。データ取得後にPHP側で「holiday_flgが1の場合は休業日 > open_flgが1の場合は営業中 > その他は受付時間外」にすれば判別できる。

条件検索とソート

条件検索はHAVINGをソートはORDER BYで指定できる。記述の順番で少々詰まったがGROUP BYの後にHAVING、HAVINGの後にORDER BYで指定できた。

営業中のデータのみ抽出したい場合

「営業終了時間からラストオーダーの時間を減算」等のややこしい判定はいらず、営業時間内で尚且つ休業日じゃないデータを抽出したい場合はサブクエリの方がシンプルに記述できる。

SELECT *
FROM shop
WHERE start_time <= now() 
AND end_time >= now()
AND NOT EXISTS (
    SELECT * 
    FROM holiday
    WHERE holiday = "2020-09-12" 
    AND shop.id = holiday.shop_id
);

NOT EXISTS～の部分で休業日に検索日(2020-09-12)が含まれている店舗を除外している。

所感

今回の案件はソートや条件検索が複数あった為、結合してif文で色々データ整形してというややこしい形を取ったが、サブクエリの方が今後使う機会が多そうに思われる。