restaurant table availability check for a particular time using sql

Question

I am trying to build a restaurant table management application using yii framework currently i am stuck with retrieving availability of tables for a particular time

i have the following tables

below image shows the table structure i have

i am getting the avaiable table by using the following piece of code

    $model = new Table;
    $criteria = new CDbCriteria;
    $criteria->condition = 'floor_id=' . $floorid;
    $rows = $model->model()->with(array(
                'bookingTables' => array(
                    'condition' => 'bookingTables.table_id IS NULL'
                    )))->findAll($criteria);

this works only when the table ids are not present in the booking_table table

suppose if say the booking_table table has table ids filled then above code will not work it will always return as not available (no clue how to use time to get available tables)

so how can i get the available tables for a particular time period any solution in yii or sql

Answer 1

Since you accept both SQL and YII, I give you a solution in SQL.

If you want to check availability at a given time, you need to know the reservation date (that includes the hour), so let's consider that we add the field 'reservation_date' in your 'booking_table' table, and let's consider a booking ends 2 hours after it has started. This is what I'll do in SQL :

SELECT t.table_id
FROM TABLE AS t
LEFT OUTER JOIN booking_table AS b ON b.booking_table_id = t.table_id
WHERE b.booking_id IS NULL
    OR DATE_ADD(NOW(), INTERVAL 2 HOUR) > b.reservation_date

This query lists all available tables at the time its executed. If you want to check availability at a specific time, replace 'NOW()' by whatever you need.