(Basic overview of database schema at http://pastebin.ca/278296)
I have, additionally, a table of "unavailability" filters:
UNAVAILABLE
===========
(-1 == ALL)
id itemid periodid startdate enddate weekdays
-- ------ -------- --------- ------- --------
1 -1 -1 2006-12-01 2006-12-31 :Sun:Sat:
...
(Summary of example row: All items are unavailable (during all periods) on weekends in December 2006.
Basically, I want to scan all items through these matching "filters" before returning them to the user. I would rather do this at the SQL side, but I could retrieve these filters into my application if necessary. It should only return rows which do not fulfill any of the relevant unavailability criteria. I'd rather do this on the SQL side to save time (filters will have to be requested for each potential item), and to simplify my application itself.
~~~
Working, non-filtered query:
SELECT *
FROM drupal_booking_bookings
RIGHT JOIN drupal_booking_dates ON drupal_booking_dates.date = drupal_booking_bookings.date
RIGHT JOIN drupal_booking_periods ON drupal_booking_periods.periodid = drupal_booking_bookings.periodid
RIGHT JOIN drupal_booking_items ON drupal_booking_items.itemid = drupal_booking_bookings.itemid
INNER JOIN drupal_booking_categories ON drupal_booking_categories.categoryid = drupal_booking_items.categoryid
WHERE drupal_booking_bookings.bookingid IS NULL
AND drupal_booking_periods.periodid = '2'
AND drupal_booking_categories.categoryid = '3'
AND drupal_booking_dates.date >= NOW()
ORDER BY drupal_booking_dates.date
LIMIT 0, 30
Attempt at filtering:
SELECT *
FROM drupal_booking_bookings
RIGHT JOIN drupal_booking_dates ON drupal_booking_dates.date = drupal_booking_bookings.date
RIGHT JOIN drupal_booking_periods ON drupal_booking_periods.periodid = drupal_booking_bookings.periodid
RIGHT JOIN drupal_booking_items ON drupal_booking_items.itemid = drupal_booking_bookings.itemid
INNER JOIN drupal_booking_categories ON drupal_booking_categories.categoryid = drupal_booking_items.categoryid
CROSS JOIN drupal_booking_unavailable
WHERE drupal_booking_bookings.bookingid IS NULL
AND drupal_booking_periods.periodid = '2'
AND drupal_booking_categories.categoryid = '3'
AND drupal_booking_dates.date >= NOW( )
AND (
drupal_booking_unavailable.weekdays NOT LIKE DATE_FORMAT( drupal_booking_dates.date, '%%:%a:%%' )
AND drupal_booking_dates.date
BETWEEN drupal_booking_unavailable.startdate
AND drupal_booking_unavailable.enddate
AND drupal_booking_unavailable.periodid
IN (
'-1', drupal_booking_periods.periodid
)
AND drupal_booking_unavailable.itemid
IN (
'-1', drupal_booking_items.itemid
)
)
ORDER BY drupal_booking_dates.date
LIMIT 0 , 30