[解決済み] カレンダーの繰り返し/繰り返しイベント - 最適な保存方法

質問

カスタムイベントシステムを構築しているのですが、以下のような繰り返しイベントがある場合。

イベントAは2011年3月3日から4日おきに繰り返される

または

イベントBは2011年3月1日から2週間おきに火曜日に開催されます。

どのようにすれば、簡単に検索できるようにデータベースに保存できますか？イベントが大量にあり、カレンダーをレンダリングする際に一つひとつを確認しなければならないような、パフォーマンスの問題は避けたいのです。

どのように解決するのですか？

単純な繰り返しパターンの格納

PHP/MySQLベースのカレンダーでは、繰り返し行われるイベント情報をできるだけ効率的に保存したいと考えました。行の数は多くしたくありませんでしたし、特定の日に行われるすべてのイベントを簡単に調べたいのです。

以下の方法は、毎日、毎正時、毎正時など、一定間隔で発生する繰り返し情報を格納するのに適しています。 n 日、毎週、毎月、毎年、などなど。これは、毎週火曜日と木曜日タイプのパターンも含みます。なぜなら、それらは火曜日から始まる毎週と木曜日から始まる毎週として別々に保存されるからです。

という2つのテーブルがあると仮定します。 events このように

ID    NAME
1     Sample Event
2     Another Event

というテーブルと events_meta このように

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000
2     1             repeat_interval_1  432000

repeat_startはunixタイムスタンプのように時間のない日付で、repeat_intervalは間隔を秒単位で表した量です（432000は5日です）。

repeat_interval_1 は、ID 1 の repeat_start に対応する。したがって、毎週火曜日と木曜日に繰り返されるイベントがある場合、repeat_intervalは604800（7日）となり、2つのrepeat_startと2つのrepeat_intervalが存在することになります。テーブルはこのようになります。

ID    event_id      meta_key           meta_value
1     1             repeat_start       1298959200 -- This is for the Tuesday repeat
2     1             repeat_interval_1  604800
3     1             repeat_start       1299132000 -- This is for the Thursday repeat
4     1             repeat_interval_3  604800
5     2             repeat_start       1299132000
6     2             repeat_interval_5  1          -- Using 1 as a value gives us an event that only happens once

そして、毎日ループするカレンダーがあり、その日のイベントを取得する場合、クエリーは次のようになります。

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.`meta_value` )
          END
        ) / EM2.`meta_value`
    ) = 1
LIMIT 0 , 30

置き換え {current_timestamp} を現在の日付の unix タイムスタンプに置き換えます (時刻を差し引くと、時、分、秒の値は 0 になります)。

これが他の誰かの助けになることを願っています。

複雑な繰り返しパターンを保存する

この方法は、次のような複雑なパターンを保存するのに適しています。

Event A repeats every month on the 3rd of the month starting on March 3, 2011

または

Event A repeats Friday of the 2nd week of the month starting on March 11, 2011

上記のシステムと組み合わせることで、最も柔軟に対応できるようになると思います。このためのテーブルは次のようなものです。

ID    NAME
1     Sample Event
2     Another Event

というテーブルと events_meta このように

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000 -- March 3rd, 2011
2     1             repeat_year_1      *
3     1             repeat_month_1     *
4     1             repeat_week_im_1   2
5     1             repeat_weekday_1   6

repeat_week_im は当月の週を表し、1〜5の可能性があります。 repeat_weekday は曜日で、1〜7です。

ここで、日/週をループしてカレンダーの月表示を作成すると仮定すると、次のようなクエリを作成することができます。

SELECT EV . *
FROM `events` AS EV
JOIN `events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key = 'repeat_start'
LEFT JOIN `events_meta` EM2 ON EM2.meta_key = CONCAT( 'repeat_year_', EM1.id )
LEFT JOIN `events_meta` EM3 ON EM3.meta_key = CONCAT( 'repeat_month_', EM1.id )
LEFT JOIN `events_meta` EM4 ON EM4.meta_key = CONCAT( 'repeat_week_im_', EM1.id )
LEFT JOIN `events_meta` EM5 ON EM5.meta_key = CONCAT( 'repeat_weekday_', EM1.id )
WHERE (
  EM2.meta_value =2011
  OR EM2.meta_value = '*'
)
AND (
  EM3.meta_value =4
  OR EM3.meta_value = '*'
)
AND (
  EM4.meta_value =2
  OR EM4.meta_value = '*'
)
AND (
  EM5.meta_value =6
  OR EM5.meta_value = '*'
)
AND EM1.meta_value >= {current_timestamp}
LIMIT 0 , 30

これを上記の方法と組み合わせることで、ほとんどの繰り返し/反復イベントのパターンをカバーすることができます。もし何か見逃していることがあれば、コメントを残してください。