# How to model indexes for complex availability searches **URL:** \ **Category:** Elasticsearch\ **Created:** [May 11, 2012, 10:40am UTC](https://discuss.elastic.co/t/how-to-model-indexes-for-complex-availability-searches/7658 "2012-05-11T10:40:34Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Donald\_Piret](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Donald\_Piret](https://discuss.elastic.co/u/Donald_Piret)\ **Post date:** [May 11, 2012, 10:40am UTC](https://discuss.elastic.co/t/how-to-model-indexes-for-complex-availability-searches/7658/1 "2012-05-11T10:40:34Z")
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? ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [May 15, 2012, 7:33pm UTC](https://discuss.elastic.co/t/how-to-model-indexes-for-complex-availability-searches/7658/2 "2012-05-15T19:33:37Z")
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.com](mailto:donald.piret@gmail.com)wrote: > 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? ---
**Author:** ![lonre](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lonre/32/7099_2.png) [@lonre](https://discuss.elastic.co/u/lonre)\ **Post date:** [February 23, 2016, 1:48pm UTC](https://discuss.elastic.co/t/how-to-model-indexes-for-complex-availability-searches/7658/3 "2016-02-23T13:48:41Z")
Years has passed, any suggestion for this? Thanks~ ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 11:14pm UTC](https://discuss.elastic.co/t/how-to-model-indexes-for-complex-availability-searches/7658/4 "2017-07-05T23:14:14Z")