So I've got an interesting problem that I might need some help with.
I'm trying to figure out how to model the indexes and queries for our
search problem but am pretty much at a loss so would really appreciate if
anyone could point me in the right direction.
Here's how our data works:
We've got a room model and an availability association, each room can have
many availability records where each availability record represents
availability at a certain date.
Additionally each availability record contains pricing information for that
Now I need to be able to make a query to return all the room records that
are available between a certain start and end date and can define a range
for the pricing.
A room is available only if there are availability record for each day
between the start and end date query, and the pricing is calculated by
taking the sum of all the pricing fields in the availability records
divided by the length of the stay.
So for example:
Room A has availability records with pricing 100 between december 12 and
Room B has availability records with pricing 100 between december 12 and
december 18, and availability record with pricing 200 between december 19
and december 24
Room C has availability records with pricing 50 between december 12 and
december 13, and december 20 - december 24.
Now I need to be able to query for the date range december 20 - december 24
with a maximum price of 120.
This should return room A, but not room B and room C (room B has an average
nightly rate greater than 120, and room C is missing some availability
Could anyone tell me how I would get to modeling this?