I was writing an availability test with starttime-endtime, on a database with ICS schedule data from Google Calendar (userid, eventid, starttime, endtime), and i came up with this one :
- get all user id’s
- a get id’s for test.start between table.start and table.end
- b get id’s for test.end between table.start and table.end
- c get id’s for test.start < table.start and test.end > table.end
- merge the three overlapping sets (a b c)
- available = the difference between all id’s and the merged set
It works but it takes four queries on the entire table.
Isn’t there a more intelligent way of doing that ?
-
function maat_agenda_check_available($start, $end) {
-
-
global $wpdb;
-
//get all id's
-
$sql = "SELECT userid FROM ".$wpdb->prefix."maat_agenda GROUP BY userid";
-
$all = $wpdb->get_results($sql, ARRAY_A);
-
-
//get ids with overlaps
-
$a = "SELECT userid FROM ".$wpdb->prefix."maat_agenda WHERE endtime > ".$start." AND starttime < ".$start." GROUP BY userid";
-
$b = "SELECT userid FROM ".$wpdb->prefix."maat_agenda WHERE endtime > ".$end." AND starttime < ".$end." GROUP BY userid";
-
$c = "SELECT userid FROM ".$wpdb->prefix."maat_agenda WHERE endtime < ".$end." AND starttime > ".$start." GROUP BY userid";
-
-
-
$rsta = $wpdb->get_results($a, ARRAY_A);
-
$rstb = $wpdb->get_results($b, ARRAY_A);
-
$rstc = $wpdb->get_results($c, ARRAY_A);
-
-
//merge arrays
-
if(!is_array($rsta)) $rsta = array();
-
if(!is_array($rstb)) $rstb = array();
-
if(!is_array($rstc)) $rstc = array();
-
$rst = array_merge($rsta, $rstb, $rstc);
-
-
//get uniques
-
array_unique($rst);
-
-
return array_diff($all, $rst);
-
-
}