勉強したことのメモ

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

MySQLで1対多のデータ結合と条件検索

      2020/09/25

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

 

具体例

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

shopテーブルのidとholidayテーブルのshop_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で指定できた。

 

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

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

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

 

所感

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

 - MySQL, データベース

  関連記事

MySQLのintのカラムで2147483647の値

MySQLでintのカラムに数値を入れると「2147483647」という値になっ ...

WordPressで「データベース接続確立のエラー」

WordPressで設定ファイル(wp-config.php)は触っていないのに ...

MySQLでCSVから列を指定してインポート

MySQLでCSVからデータをインポートしたかった。またエクセル風にいうA列はi ...

MySQLでグループ毎に連番を設定する方法

MySQLでオートインクリメントを使用せずにグループ毎で連番を割り振りたかった。 ...

MySQLで大文字小文字

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

市区町村のMySQL用データが欲しい

都道府県と市区町村が連携したセレクトメニューを作成したかった。具体的には大阪府を ...

MySQLで同一サーバーの異なるDB内のテーブルをJOINする

同一のMySQLサーバーでdb1データベースのtable1テーブルと、db2デー ...

MySQLでパターンマッチの逆

LIKE検索の逆ってなったのでメモ。 NOT LIKE '%あああ%' が正解み ...

MySQLのエラーログ取得関数

場合によってログに出力させたい値が異なる場合があったのでメモ。 function ...

MySQLでWhere句を使ってカラム指定でデータコピー

MySQLで大量のデータを保存しているテーブルがあり、そのデータを絞った上で尚且 ...