I have a ParkingLot
model. Parking Lots have a number of available lots
. Users can then book a parking lot for one or more days. Hence I have a Booking
model.
class ParkingLot
has_many :bookings
end
class Booking
belongs_to :parking_lot
end
Simplified Usecase
ParkingLot
Given a parking lot with 5 available lots:
Bookings
- Bob books a place from Monday to Sunday
- Sue makes one booking each on Monday, Wednesday and Friday
- Henry books only on Friday.
- Since the weekend is busy, 4 other people book from Saturday to Sunday.
Edit
The bookings have a start_date
& an end_date
, so Bob's bookings only has one entry. Mon-Sun
.
Sue on the other hand really has three bookings, all starting and ending on the same day. Mon-Mon
, Wed-Wed
, Fri-Fri
.
This gives us following booking data:
For simplicity, instead of the user_id (1
) & the date (2015-5-15
), I will use the initial (B
) and the week days (Mon
).
––––––––––––––––––––––––––––––––––––––––––
| id | user_id | start_date| end_date| ... |
|––––––––––––––––––––––––––––––––––––––––––|
| 1 | B | Mon | Sun | ... |
|––––––––––––––––––––––––––––––––––––––––––|
| 2 | S | Mon | Mon | ... |
| 3 | S | Wed | Wed | ... |
| 4 | S | Fri | Fri | ... |
|––––––––––––––––––––––––––––––––––––––––––|
| 5 | H | Fri | Fri | ... |
|––––––––––––––––––––––––––––––––––––––––––|
| 6 | W | Sat | Sun | ... |
| 7 | X | Sat | Sun | ... |
| 8 | Y | Sat | Sun | ... |
| 9 | Z | Sat | Sun | ... |
––––––––––––––––––––––––––––––––––––––––––
This gives us the following week:
–––––––––––––––––––––––––––––––––––––––––
| Mon | Tue | Wed | Thu | Fri | Sat | Sun |
|–––––––––––––––––––––––––––––––––––––––––|
| B | B | B | B | B | B | B |
|–––––––––––––––––––––––––––––––––––––––––|
| S | - | S | - | S | - | - |
|–––––––––––––––––––––––––––––––––––––––––|
| - | - | - | - | H | - | - |
|–––––––––––––––––––––––––––––––––––––––––|
| - | - | - | - | - | W | W |
| - | - | - | - | - | X | X |
| - | - | - | - | - | Y | Y |
| - | - | - | - | - | Z | Z |
|=========================================|
| 2 | 1 | 2 | 1 | 3 | 5 | 5 | # Bookings Count
|=========================================|
| 3 | 4 | 3 | 4 | 2 | 0 | 0 | # Available lots
–––––––––––––––––––––––––––––––––––––––––
These bookings are already in the database, so when a new user wants to book from Monday to Friday, there is space to do so. But when he wants to book from Monday to Saturday, this will not be possible.
My goal is to query for the max number of bookings in a given time range. Ultimately leading to the available lots
# Mon - Thursday => max bookings: 2 => 3 available lots
# Mon - Friday => max bookings: 3 => 2 available lots
# Mon - Sunday => max bookings: 5 => 0 available lots
A simple, but wrong approach of mine was to get all bookings that fall in the given time range:
scope :in_range, ->(range) { where("end_date >= ?", range.first).where("start_date <= ?", range.last) }
But this is by no means correct. Querying from Monday to Friday returns 5 bookings, one from Bob, one from Henry and three from Sue. This would falsely assume the Parking Lot is full.
How would I create such a query to get the max count of bookings in a given time range?
This can also be pure SQL
, I'll be happy to translate it into AR
lateron.
You need GROUP by day since your bookings are daily based. Check the total bookings in a specific day against your total lots, you get available space for that day.
Let create a table bookings with following entries:
Do this query:
will give you something:
You know now how many lots available for each day.
There is one issue to solve, if there is no booking for a specific day, it will not be listed in above result, you need add a calendar table or build a small temp table to solve it.
Use this to generate a table for next 7 days:
and check your code to
It will give you something like :
(The last results is generated using different sample data)
This is a modified version :
gives you results like:
I think this can be simplified with a modified data model
To make the example more clear I have changed the names of the Classes to be more descriptive.
We will use classes:
The relationships should be
Then you can do something along the lines of:
I am going to assign some numerical values to the days to simplify this problem. You can make this the 'id' column (or something like that).
Now it's a really simple SQL:
This is Mon to Thu, and you can keep changing the range to get the available lots between a certain period. Bob's requirement can be plugged in place of the values 1 and 4, and he will know if there is an available lot or not.
I would assume that you are going to apply this to a large scale solution and if you are actually using date, the above query can be very easily adapted to get the correct value each time.
There is a simple way using a calendar table. If you don't have one already you should create it, it has multiple usages.
Edit: Vladimir Baranov suggested to add a link on how to create and use a calendar table. Of course the actual implementation is always user and DBMS specific (e.g. MS SQL Server), so searching for "calendar table" + yourDBMS will probably reveal some source code for your system.
In fact the easiest way to create a calendar table is to do the calculation for the range of years you need in a spreadsheet (Excel, etc. go all the functions you need like easter calculation) and then push it to the database, it's a one-time operation :-)
Rails use case¹
First, create the
CalendarDay
model. I've added more columns than just theday
, which may come in handy for future scenarios.db/migrate/201505XXXXXX_create_calendar_days.rb
Then, after running
rake db:migrate
add a rake task to populate your model:lib/tasks/calendar_days.rake
And run
calendar_days:populate
Lastly, you can use Activerecord to perform complex queries as the one above:
1 - Use case added by TheChamp