MySQL schedule conflicts

Question

Hey, I stumbled upon this site looking for solutions for event overlaps in mySQL tables. I was SO impressed with the solution (which is helping already) I thought I'd see if I could get some more help...

Okay, so Joe want's to swap shifts with someone at work. He has a court date. He goes to the shift swap form and it pull up this week's schedule (or what's left of it). This is done with a DB query. No sweat. He picks a shift. From this point, it gets prickly.

So, first, the form passes the shift start and shift end to the script. It runs a query for anyone who has a shift that overlaps this shift. They can't work two shifts at once, so all user IDs from this query are put on a black list. This query looks like:

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

Next, we run a query for all shifts that are a) the same length (company policy), and b) don't overlap with any other shifts Joe is working.

What I currently have is something like this:

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

Now, you are probably wondering "what is $conflict_dates???"

Well, when Joe submits the swap shift, it reloads his shifts for the week in case he decides to check out another shift's potential. So when it does that first query, while the script is looping through and outputting his choices, it is also building a string that looks kind of like:

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

So that the database is getting a pretty long query along the lines of:

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

So, my hope is that either SQL has some genius way of dealing with this in a simpler way, or that someone can point out a fantastic logical principal that accounts for the potential conflicts in a much smarter way. (Notice the use of the 'start > end, end < start', before I found that I was using betweens and had to subtract a minute off both ends.)

Thanks!

A

Answer 1

You could load the joe_shift{1,2,3} values into a TEMPORARY table and then do a query to join against it, using an outer join to find only shift that don't match any:

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');

Because of the LEFT OUTER JOIN, when there is no matching row in joes_shifts, the columns are NULL.

Answer 2

I think you should be able to exclude Joe's other shifts using an inner select instead of the generated string, something like:

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

Basically, each row has an inner query for the count of Joe's shifts which overlap, and makes sure that it's zero. Thus, only rows which don't overlap with any of Joe's existing shifts will be returned.