130単位

SQL 期間の重複をチェック

開発, SQL

EC-CUBEにはキャンペーン機能があります。カスタマイズ要件として、新規登録時に既存のキャンペーンとは期間がかぶらないようにしたい、というものがありました。

dtb_campaign

テーブルを簡略化したものが上記です。このようなデータがある場合だと、7/1以前に期間が終了するか、8/31以降に期間が開始されるキャンペーンだけが登録できればいいことになります。

フォームから受け取って、さらにDB用に整形した値を以下とします(PHPです)。

• $start_date…キャンペーン開始日時
• $end_date…キャンペーン終了日時

SQLは、重複するデータの有無をカウントをとって調べるものとします。最初に考え付いたのがこちら↓。

SELECT COUNT(campaign_id) FROM dtb_campaign
WHERE ((start_date BETWEEN $start_date AND $end_date)
    OR (end_date BETWEEN $start_date AND $end_date)
    OR (start_date <= $start_date AND end_date >= $end_date))
AND del_flg = 0

これは…明らかにスマートではないですね。実は最初は4行目は無くて、それだと例えば「7/15〜8/15」とかをスルーされてしまうのに気づき、無理やり追加したというかたちです。

で、時間をおいて考えてみた改良版がこちら↓。

SELECT COUNT(campaign_id) FROM dtb_campaign
WHERE (($start_date BETWEEN start_date AND end_date)
    OR ($end_date BETWEEN start_date AND end_date))
AND del_flg = 0

発想の転換をして、BETWEENの期間のほうにテーブルのフィールドを持ってきてみました。最初のSQLよりもスッキリしていますし、結果は正しく取れるはずです。はず、というのは、まだ試してないからです*1。 →追記へ

ちなみに、既存のキャンペーンを編集→更新する際の重複チェックは、キーとなる$campaign_idがセットされているかどうかで判断し、WHERE句に「AND campaign_id <> $campaign_id」を追加するようにすればいいと思います。

【追記】

すいませんできませんでした。「6/1〜9/1」のように期間を覆う場合をスルーしてしまいました。さらに改良したのがこちら↓。

SELECT COUNT(campaign_id) FROM dtb_campaign
WHERE (start_date <= $end_date AND end_date >= $start_date)
AND del_flg = 0

実際に試したのでできている(はず)です。詳しくは次の記事を予定。

ツイートする

Permalink | コメント(0) | トラックバック(0)