Hello to all. Help advice.

There is a database (MySQL) in which there is a table (events) with a list of events that have a start date (date_start) and an end date (date_end). In addition, some events should have a cyclical nature (the same recording, which is displayed several times, but with different dates, for example, a meeting from 12:00 to 14:00 every Friday (repeat_every = 'week', repeat_day = 5) in the period from 05/01/2016 (repeat_start) until 01.02.2017 (repeat_end)).

Now every day the cron is triggered and creates a copy of the original event a day before its occurrence. The question is how, using SELECT, without creating unnecessary copies and with minimal loss in performance, output events with upcoming dates in a given range.

  • one
    What prevents you from adding the required number of records at the time of recording a cyclical event - for the entire period? or, if the period has an open date, then to some definite in the distant future ... - Akina
  • @Akina No, making copies of the same record in advance will not work. What will we do if we need to make changes to the cycle? - check1st
  • @ check1st to make changes to the event definition will mean deleting existing entries for this event and creating new ones. Yes, this is quite a lot of work. BUT, there is a principle: it is necessary to optimize what is often repeated. So, most often we will watch all events for a certain period and very rarely we will rule the event. - artoodetoo

2 answers 2

(auxiliary operation) To accomplish a task within a single query, it will be required in the subquery to generate a table with numbers in an amount not less than the maximum possible number of repetitions in a given period. For example, if it is obviously less than one hundred, then:

SELECT t1+10*t2 number FROM (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) t1 , (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) t2

==============

In the main query, based on the table of event templates and a subquery with numbers, based on the template, we construct an expression for calculating the repeat date number, if it does not fly out of borders. Type:

 SELECT DATEADD(e.repeat_start, INTERVAL n.number WEEK) date FROM events e, (подзапрос генерации чисел) n WHERE date <= e.repeat_end

And ... everything, sobsno.

There are, of course, problems. At least two.

The first is that the period size (in the template shown above, this is WEEK) cannot simply be taken from the table, because it is not a string literal. We'll have to generate a bunch of CASE-s select the desired code depending on the value of repeat_every. Or for each value write your request, and union them.

The second is that the start date can easily not fall into the condition (for example, if repeat_day = 5, the date in the repeat_start will NOT be Thursday). This (search for the nearest date that meets the condition, but more than the start date) is solved by a simple expression, again individual for each repeat_every.

    If we are talking about events that already exist in the database, you can use BETWEEN to select a range.
    If we are talking about events that have not yet been recorded in the database, then it is a bad tone to show what is not really, because something can go wrong and the event will not take place. If it is still necessary to implement such a scheme, use, for example, temporary tables.
    Another possible option is to repeat the logic of building tasks in PHP using \ DateTime. You can, for example, shift the current date 3 days ahead.

     $date = new \DateTime('+3 day'); echo $date->format('Ymd H:i:s');

    You can also move a week ahead, a month, a year, or whatever. If it is necessary to shift N times by the same span, use the cycle. Write all results to an array and display it.