勉強したことのメモ

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, データベース

  関連記事

MySQLiでDELETE文

MySQLiでDELETE文 ■ソース $mysqli = new mysqli ...

MySQLiでSELECT文

MySQLiでSELECT文 ■データ +--+------+ | id | p ...

MySQLで検索した件数の取得

ページングとかで必要になる総件数について、 いつもググってしまっていたのでメモ。 ...

MySQLで開始から終了日付内を調べたいとき

等号不等号でもできるけど、BETWEENというのがよさそう。 ■参考サイト ht ...

お名前SDサーバにMySQLデータのインポート

お名前.comのレンタルサーバにそこそこ大きいサイズのMySQLデータを移行した ...

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

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

MySQLで文字列の置換

MySQLで文字列の置換を行いたかった。以下に方法をメモ。   置換方 ...

MySQLで大文字小文字

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

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

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

adminerでMySQLに接続しようとするとエラー

新しく構築したサーバにadminerを入れて接続しようとすると、「mysqlnd ...