How to model availability in a simple hostel booking system? (Laravel 5.4)

I've got a simple hostel booking system and am not sure how to model availability in the database (e.g. via Eloquent). A hostel is simpler than a hotel as for booking purposes it's effectively just one big room. However there will be multiple hotels in the database (run by different owners). So my current database tables are: Guests, Hostels (includes owner details), Bookings (i.e. person W staying at hostel X from date Y to date Z), and Payments (payment amount for a stay). Obviously these are tied together (e.g. with ID references or some other relationship).

But with availability I need to represent the number of beds available for a specific hostel on any date for e.g. the next year or two. What's an efficient way to do that?

Thanks for any suggestions.

sutherland

8 years ago

Level 28

Store a beds attribute with each hostel, then you can sum bookings for a hostel grouped by date and subtract that number.

ajck

8 years ago

Level 1

@sutherland Thanks. I don't get the 'grouped by date' bit - how am I storing the availability for each separate day for a year (or two)? Any example code? :)

Thanks

sutherland

8 years ago

Level 28

If you want to query a specific date you could do:

public function availabilityOnDate($date)
{
    return $this->beds - $this->bookings()
        ->whereDate('date', $date)
        ->count();
}

I haven't tested this, but If you want to get availability for a range of dates I think it would be something like so:

public function countDailyBookingsBetween($start, $end)
{
    return $this->bookings()
        ->select('date', \DB::raw('count(*) as count'))
        ->whereDate('date', '>', $start)
        ->whereDate('date', '<', $end)
        ->groupBy('date')
        ->orderBy('date', 'ASC')
        ->getQuery()
        ->get();
}

Grouping the query by date means that any two records (bookings) that have the same date will be merged together, and as you can see we'll select the count of each of those dates.

Please or to participate in this conversation.