Конфликты расписания MySQL

Эй, я наткнулся на этот сайт в поисках решений для перекрытия событий в таблицах mySQL. Я был НАСТОЛЬКО впечатлен решением (которое уже помогает), я подумал, что посмотрю, смогу ли я получить дополнительную помощь...

Итак, Джо хочет поменяться сменами с кем-нибудь на работе. У него дата суда. Он идет к форме обмена сменами, и она показывает расписание на эту неделю (или то, что от него осталось). Это делается с помощью запроса к БД. Нет пота. Он выбирает смену. С этого момента он становится колючим.

Итак, сначала форма передает скрипту начало и конец смены. Он выполняет запрос для всех, у кого есть смена, перекрывающая эту смену. Они не могут работать в две смены сразу, поэтому все ID пользователей из этого запроса заносятся в черный список. Этот запрос выглядит так:

SELECT DISTINCT user_id FROM shifts
WHERE
FROM_UNIXTIME('$swap_shift_start') < shiftend
AND FROM_UNIXTIME('$swap_shift_end') > shiftstart

Затем мы запускаем запрос для всех смен, которые а) имеют одинаковую продолжительность (политика компании) и б) не пересекаются ни с какими другими сменами, в которых работает Джо.

В настоящее время у меня есть что-то вроде этого:

SELECT *
FROM shifts
AND shiftstart BETWEEN  FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ($busy_users) 
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
$conflict_dates
ORDER BY shiftstart, lastname

Теперь вы, вероятно, задаетесь вопросом: «Что такое $conflict_dates???».

Что ж, когда Джо отправляет сменную смену, он перезагружает его смены на неделю на случай, если он решит проверить потенциал другой смены. Поэтому, когда он выполняет этот первый запрос, в то время как скрипт перебирает и выводит его варианты, он также создает строку, которая выглядит примерно так:

AND NOT(
'joe_shift1_start' < shiftend
AND 'joe_shift1_end' > shiftstart)
AND NOT(
'joe_shift2_start' < shiftend
AND 'joe_shift2_end' > shiftstart)
...etc

Так что база данных получает довольно длинный запрос по строкам:

SELECT *
FROM shifts
AND shiftstart BETWEEN  FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ('blacklisteduser1', 'blacklisteduser2',...etc) 
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
AND NOT(
'joe_shift1_start' < shiftend
AND 'joe_shift1_end' > shiftstart)
AND NOT(
'joe_shift2_start' < shiftend
AND 'joe_shift2_end' > shiftstart)
AND NOT(
'joe_shift3_start' < shiftend
AND 'joe_shift3_end' > shiftstart)
AND NOT(
'joe_shift4_start' < shiftend
AND 'joe_shift4_end' > shiftstart)
...etc
ORDER BY shiftstart, lastname

Итак, я надеюсь, что либо в SQL есть какой-то гениальный способ справиться с этим более простым способом, либо кто-то может указать фантастический логический принцип, который гораздо умнее объясняет потенциальные конфликты. (Обратите внимание на использование «начало> конец, конец ‹ начало», прежде чем я обнаружил, что использую промежутки и должен был вычесть минуту с обоих концов.)

Спасибо!

A


mysql calendar datetime schedule
person Anthony    schedule 28.12.2008    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Я думаю, вы должны иметь возможность исключить другие смены Джо, используя внутренний выбор вместо сгенерированной строки, например:

SELECT *
FROM shifts s1
AND shiftstart BETWEEN  FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND user_id NOT IN ($busy_users) 
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = '$swap_shift_length')
AND (SELECT COUNT(1) FROM shifts s2
     WHERE s2.user_id = $joes_user_id
     AND   s1.shiftstart < s2.shiftend
     AND   s2.shiftstart < s1.shiftend) = 0
ORDER BY shiftstart, lastname

По сути, каждая строка имеет внутренний запрос на количество перекрывающихся смен Джо и гарантирует, что оно равно нулю. Таким образом, будут возвращены только те строки, которые не пересекаются ни с одной из существующих смен Джо.

person Adam Bellaire    schedule 28.12.2008
comment
Я не мог понять, почему это не работает. Вот что я получаю за копирование и вставку. Для $joes_user_id нужны кавычки. В любом случае ... ЭТО РАБОТАЕТ! Я, честно говоря, не совсем понимаю. Я понял основную идею, но я попробовал подзапрос для поиска черного списка, но это заняло НАВСЕГДА. Но это не так. - person Anthony; 29.12.2008
comment
В любом случае, спасибо за совет. И Биллу тоже. Я определенно сейчас читаю Temporary Tables. - person Anthony; 29.12.2008

arrow_upward
1
arrow_downward

Вы можете загрузить значения joe_shift{1,2,3} во ВРЕМЕННУЮ таблицу, а затем выполнить запрос для объединения с ней, используя внешнее соединение, чтобы найти только смену, которая не соответствует ни одной:

CREATE TEMPORARY TABLE joes_shifts (
 shiftstart DATETIME
 shiftend   DATETIME
);
INSERT INTO joes_shifts (shiftstart, shiftend) VALUES
  ('$joe_shift1_start', '$joe_shift1_end'),
  ('$joe_shift2_start', '$joe_shift2_end'),
  ('$joe_shift3_start', '$joe_shift3_end'),
  ('$joe_shift4_start', '$joe_shift4_end');
-- make sure you have validated these variables to prevent SQL injection

SELECT s.*
FROM shifts s
  LEFT OUTER JOIN joes_shifts j
  ON (j.shiftstart < s.shiftend OR j.shiftend > s.shiftstart) 
WHERE j.shiftstart IS NULL
  AND s.shiftstart BETWEEN FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
  AND s.user_id NOT IN ('blacklisteduser1', 'blacklisteduser2',...etc) 
  AND (TIME_TO_SEC(TIMEDIFF(s.shiftend,s.shiftstart)) = '$swap_shift_length');

Из-за LEFT OUTER JOIN, когда нет соответствующей строки в joes_shifts, столбцы имеют значение NULL.

person Bill Karwin    schedule 28.12.2008