How to model indexes for complex availability searches

Elastic Stack Elasticsearch
1

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
particular date.

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
december 24
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
records).

Could anyone tell me how I would get to modeling this?

1 Like
2

Heya,

Its a bit tricky, and I can think of some complications (for example,
rooms where the rate changes between dates that someone is searching on).
You could index availability records with start date, end date, price, and
room id (to link back to the room), and then do range filters on the price
and dates.

On Fri, May 11, 2012 at 1:40 PM, Donald Piret donald.piret@gmail.comwrote:

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 particular date.

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
december 24
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 records).

Could anyone tell me how I would get to modeling this?

3

Years has passed, any suggestion for this?

Thanks~