availability test

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 ?

  1. function maat_agenda_check_available($start, $end) {
  2.  
  3. global $wpdb;
  4. //get all id's
  5. $sql = "SELECT userid FROM ".$wpdb->prefix."maat_agenda GROUP BY userid";
  6. $all = $wpdb->get_results($sql, ARRAY_A);
  7.  
  8. //get ids with overlaps
  9. $a = "SELECT userid FROM ".$wpdb->prefix."maat_agenda WHERE endtime > ".$start." AND starttime < ".$start." GROUP BY userid";
  10. $b = "SELECT userid FROM ".$wpdb->prefix."maat_agenda WHERE endtime > ".$end." AND starttime < ".$end." GROUP BY userid";
  11. $c = "SELECT userid FROM ".$wpdb->prefix."maat_agenda WHERE endtime < ".$end." AND starttime > ".$start." GROUP BY userid";
  12.  
  13.  
  14. $rsta = $wpdb->get_results($a, ARRAY_A);
  15. $rstb = $wpdb->get_results($b, ARRAY_A);
  16. $rstc = $wpdb->get_results($c, ARRAY_A);
  17.  
  18. //merge arrays
  19. if(!is_array($rsta)) $rsta = array();
  20. if(!is_array($rstb)) $rstb = array();
  21. if(!is_array($rstc)) $rstc = array();
  22. $rst = array_merge($rsta, $rstb, $rstc);
  23.  
  24. //get uniques
  25. array_unique($rst);
  26.  
  27. return array_diff($all, $rst);
  28.  
  29. }
Posted in mysql, wordpress.

4 Comments

  1. I assume you’re using the GROUP BY to get rid of duplicate rows? If so, SELECT UNIQUE will do that for you.

    You could probably do the whole job in SQL.

    SELECT UNIQUE userid FROM wp_maat_agenda WHERE NOT EXISTS (
    SELECT UNIQUE userid FROM wp_maat_agenda WHERE endtime > “.$start.” AND starttime “.$end.” AND starttime < ".$end
    UNION
    SELECT userid FROM wp_maat_agenda WHERE endtime “.$start .”)

    I haven’t tested this at all BTW but it should set you in the right direction. Good luck.

  2. First of all. i am assuming that the purpose is:

    you specify a start and end time and the method checks if it doesn’t conflict with current events. Returns list of available users.

    This is easily done in a single query.

    best in performance is to select from the users table and left join all appointments that are inside your timeframe. next exclude those that match on the join. This uses some real efficient mysql constructs. (assuming you run mysql, otherwise it still works like a charm)

    select
    userid
    from usr u
    left join wp_maat_agenda a on
    a.userid = u.userid and
    a.enddtime > “.$start.” and
    a.starttime < ".$end."
    where a.userid is null

    also @andymurd the correct syntax is DISTINCT. Preference of Distinct over Group By is a matter of convention. Not of performance or any other reason.. so if you set your convention to use group by rather than distinct, you should use group by ;)

    UNIQUE is non-standard and deprecated, originating from old oracle nonsense. Doesn't work for most SQL dbms.

    A next part is that a UNIQUE/DISTINCT inside a subquery is a high performance risk. So please don't do that, unless you have a good reason.

    I hope this helped.

  3. @Niels @Andymurd
    thanks
    I was a bit at a loss there :)

    This one seems to work :

    SELECT ID FROM
    wp_users u
    WHERE NOT EXISTS (SELECT userid FROM
    wp_maat_agenda b
    WHERE
    b.starttime < = ".$start." AND b.endtime >= “.$start.”
    OR b.starttime >= “.$start.” AND b.endtime < = ".$end." OR b.starttime <= ".$end." AND b.endtime >= “.$end.”
    OR b.starttime < ".$start." AND b.endtime > “.$end.”
    AND u.ID = b.userid)”;

    ..as does this one :

    SELECT u.ID, b.userid FROM
    wp_users u
    LEFT OUTER JOIN
    wp_maat_agenda b
    ON b.userid = u.ID
    AND ( b.starttime < = ".$start." AND b.endtime >= “.$start.”
    OR b.starttime >= “.$start.” AND b.endtime < = ".$end." OR b.starttime <= ".$end." AND b.endtime >= “.$end.”
    OR b.starttime < ".$start." AND b.endtime > “.$end.”)
    WHERE b.userid IS NULL

    I don’t know which one performs better but both are faster than using arrays :)

  4. You know you could hire people on some forums to help you. Right now its pretty cheap to higher people for nothing to do php coding work. Alot of outsourcing companies out there i use. I have a personal guy i use on digital point when i need some coding work done.

Leave a Reply

Your email address will not be published. Required fields are marked *