在MySql中根据工作日和特定时间选择记录

问题描述：

I need to filter out specific events based on specific criteria, e.g.

[x] Mo
- [x] 12:00
- [ ] 14:00
[x] Tue
- [ ] 12:00
- [x] 15:30
[x] Fri
- [ ] 10:00
- [ ] 11:30
- [x] 17:15
- [x] 18:00

which gives the following array in php:

array(
   0 => [[12, 00]],
   1 => [[15, 30]],
   4 => [[17, 15], [18, 00]]
)

The final array may be adjusted to the needs, however now I want to select from schedule_event table which has starts_at property that stores datetime.

Question is, is it possible to select specific events, so e.g. Monday will return those starting on 12:00 (Tuesday shouldn't), Friday will return 17:15 and 18:00.

So far I had this:

... WEEKDAY(se.startsAt) IN (:weekDays) ...

And :weekDays was simply array_keys of the given array. Now, I don't know how to actually include times now.

答

You have to create pairs of weekday - hour [- minute] conditions to achieve the expected outcome:

select ...
from ...
where WEEKDAY(se.startsAt)=0 and hour(se.startsAt)=12
   or WEEKDAY(se.startsAt)=1 and hour(se.startsAt)=15 and minute(se.startsAt)=30
   ...

Instead of using hour and minute functiond, you can also use a single date_format() call, but I'll leave that to you.

答

You can no longer use WEEKDAY IN (list of days) because the weekday alone is not enough to yield a truth. You need a list of OR, and each OR is made up of a weekday condition ANDed with the hour conditions for that day, which are a list, and these last will be OR'ed together.

So you assemble the array into a SQL query, where you have to build both the SQL and the values:

array(
    0 => [[12, 00]],
    1 => [[15, 30]],
    4 => [[17, 15], [18, 00]]
)


$sets   = [ ]; // SQL conditions for each day
$values = [ ]; // List of accumulated values to send to execution

foreach ($conditions as $dayNum => $listTimes) {
   $hours = [ ];
   $values[] = (int)$dayNum;

   // Each time is an array of (hour, minute)
   // which comes handy for building the query
   foreach ($listTimes as $pair) {
       $hours[] = '(HOUR(se.startsAt) = ? AND MINUTE(se.startsAt) = ?)';
       $values[] = (int)$pair[0];
       $values[] = (int)$pair[1];
   }
   $sets[] = "(WEEKDAY(se.startsAt)=? AND (" . implode(' OR ', $hours) . "))";
}

$filter = implode(' OR ', $sets);

This will become something like (omitting arguments for clarity)

(WEEKDAY=0 AND ((HOUR=12 AND MINUTE=0)))
OR
(WEEKDAY=1 AND ((HOUR=12 AND MINUTE=0) OR (HOUR=14 AND MINUTE=30)))
...

So:

// PREPARE this
$sql = "SELECT ... WHERE {$assembledConditions}";
// And EXECUTE passing $values.

Any further values in the query will need to be placed in $values beforehand, of course.

在MySql中根据工作日和特定时间选择记录

相关推荐