勉強したことのメモ

webプログラマが勉強したことのメモ。

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文で色々データ整形してというややこしい形を取ったが、サブクエリの方が今後使う機会が多そうに思われる。

 - MySQL, データベース

  関連記事

MySQLで大文字小文字

半角英数字のみのフォームで入力した値を検索した際、 データに差異が発生。 [Aa ...

MySQLが起動も再起動もできない

サイトで障害が発生した。FTPやSSH接続は出来るけどサイト自体は表示されない。 ...

MySQLで速度改善

よく見るけど実際に対面してみて難しさが分かった。 150万件ほどのデータがあって ...

htmlspecialcharsを通してMySQLに格納したものを元に戻して出力

MySQLにHTMLタグを入れたくない場合にhtmlspecialcharsを使 ...

SortableJSを使ってデータの並べ替え&保存

あるシステムを拝見した際にtableタグ内に並んでいる項目をドラッグ&ド ...

SSH接続でMySQLにデータをインポートする

phpMyAdminが入っていなかったり、SQLファイルが大きかったりという理由 ...

MySQLでIPアドレスを数値として入れると変になる

MySQLでIPアドレスをINET_ATON関数を用いてINT型に変換し格納する ...

MySQLで月別に集計を行う。あと日付時刻関数。

現在進行形でやっているデータ集計系だけど 新たな発見があったのでメモ。 ■日付時 ...

MySQLでユーザー作成時にエラーが発生

SSHからrootアカウントでMySQLにログインし、ユーザーを作成しようとする ...

memcached関連

さくらvpsにmemcachedを入れて触ってみた際のメモ。 --------- ...