Generate a room booking recurrence and then find empty rooms for each booking

See if these help https://laracasts.com/discuss?q=booking+app

Thanks jlrdw. I've read far-and-wide on this one, even generic SQL resources. I guess if if I went more theoretical with the question what I'm trying to ask is can I query similar to the above on multiple date ranges in one query and get a list of rooms, instead of one query per date range.

A post laravel a while back went pretty deep into how to do bookings, searching is hard I know. I regret not saving that link. I think the one I remember was airline booking.

You could also search Github for things like this and get ideas from the code.

And I think @Snapey was involved in one of the previous post.

Thanks again, I appreciate your contribution. I had not thought of trying to find examples through an airline booking system which should help widen my searching. Should I find a suitable answer I will ensure to copy it here.

Ok, so I've been racking my brains quite a bit and what I've come up with is:

1. Generate the recurrences
2. Query the database for all rooms in the department
3. Create an array for each date containing the rooms room
4. Query the database for all bookings which fall in the datetime range (see below)
5. For each booking's date & room flag the relevant field in step 3 as room unavailable.
6. Output all rooms by booking with available/unavailable rooms flagged appropriately.

This works on the assumption that there isn't multiple recurrences on the same day (there isn't). It will take it down to two queries instead of a single query for each booking. Any comments welcome.

P.s. how do people name the different levels of an eloquent query? In this one I've gone $query -> $q -> oh shit, now what... alphabet? $r -> $s

        Clinic::where(function ($query) use ($Dates)
        {
            $query->where(function($q) use ($Dates)
            {
                $q->where( function($r) use ($Dates)
                {
                    $i=0;
                    foreach($Dates as $Date)
                    {
                        if($i == 0)
                        {
                            $r->where( function($s) use ($Date)
                            {
                                $s->where('StartTime', '>=', $Date->getStart()->format('Y-m-d H:i'));
                                $s->where('StartTime', '<', $Date->getEnd()->format('Y-m-d H:i'));
                            });
                            $i++;
                        } else {
                            $r->orWhere( function($s) use ($Date)
                            {
                                $s->where('StartTime', '>=', $Date->getStart()->format('Y-m-d H:i'));
                                $s->where('StartTime', '<', $Date->getEnd()->format('Y-m-d H:i'));
                            });
                            $i++;
                        }
                    }
                });
                $q->where( function($r)
                {
                    $r->where('ClinicStatus', '=', 1);
                    $r->orWhere('ClinicStatus', '=', 2);
                    $r->orWhere('ClinicStatus', '=', 4);
                });
            });
            $query->orWhere(function($q) use ($Dates)
            {
                $q->where( function($r) use ($Dates)
                {
                    $i=0;
                    foreach($Dates as $Date)
                    {
                        if($i == 0)
                        {
                            $r->where( function($s) use ($Date)
                            {
                                $s->where('EndTime', '>', $Date->getStart()->format('Y-m-d H:i'));
                                $s->where('EndTime', '<=', $Date->getEnd()->format('Y-m-d H:i'));
                            });
                            $i++;
                        } else {
                            $r->orWhere( function($s) use ($Date)
                            {
                                $s->where('EndTime', '>', $Date->getStart()->format('Y-m-d H:i'));
                                $s->where('EndTime', '<=', $Date->getEnd()->format('Y-m-d H:i'));
                            });
                            $i++;
                        }
                    }
                });
                $q->where( function($r)
                {
                    $r->where('ClinicStatus', '=', 1);
                    $r->orWhere('ClinicStatus', '=', 2);
                    $r->orWhere('ClinicStatus', '=', 4);
                });
            });
            $query->orWhere(function($q) use ($Dates)
            {
                $q->where( function($r) use ($Dates)
                {
                    $i=0;
                    foreach($Dates as $Date)
                    {
                        if($i == 0)
                        {
                            $r->where( function($s) use ($Date)
                            {
                                $s->where('StartTime', '<=', $Date->getStart()->format('Y-m-d H:i'));
                                $s->where('EndTime', ">=", $Date->getEnd()->format('Y-m-d H:i'));
                            });
                            $i++;
                        } else {
                            $r->orWhere( function($s) use ($Date)
                            {
                                $s->where('StartTime', '<=', $Date->getStart()->format('Y-m-d H:i'));
                                $s->where('EndTime', ">=", $Date->getEnd()->format('Y-m-d H:i'));
                            });
                            $i++;
                        }
                    }
                });
                $q->where( function($r)
                {
                    $r->where('ClinicStatus', '=', 1);
                    $r->orWhere('ClinicStatus', '=', 2);
                    $r->orWhere('ClinicStatus', '=', 4);
                });
            });
        })->whereHas('Room.Outpatient', function($q) use($OutpatientID) {
            $q->where('id', "=", $OutpatientID);
        })->get();

you do see the similar post on the same page don't you https://laracasts.com/discuss/channels/eloquent/hotel-rooms-booking-system