Get available apartments query

We have to list free apartments and those apartment that will be available in the desired period (start_date, end_date variables)

So it should be a or query: free_aparments or available_aparments

The free apartments (those that haven't any value in reservations field) should be easy to query with a missing filter, but this is a nested field and we have to deal with.
If we perform the query with a missing filter all docs will be returned. It's weird but it happens. Here there's the explained solution: https://gist.github.com/Erni/7484095 and here is the issue: https://github.com/elastic/elasticsearch/issues/3495 The gist snnipet works with all elasticsearch versions.

The other part of the or query are available apartments.
I've solved this part performing a not query. Return me those apartments that NOT have a reservation, thought a list of range that match with those aparments that do have a reservation and then negate the result using must_not filter

elasticsearch_query = {    "query": {        "filtered": {            "filter": {                "bool": {                    "should": [                        {                            "nested": {                                "filter": {                                    "bool": {                                        "must_not" : [                                            {                                                "range": {                                                    "start_date": {                                                        "gte" : start_date,                                                         "lt" :end_date                                                    }                                                }                                            },                                            {                                                "range": {                                                    "end_date": {                                                        "gte" : end_date,                                                         #"lte" :end_date                                                    }                                                }                                            }                                        ]                                    }                                },                                 "path": "reservations"                            }                        },                        {                            #{ "missing" : { "field" : "reservations"} }                            "not": {                                "nested": {                                    "path": "reservations",                                    "filter": {                                        "match_all": {}                                    }                                }                            }                        }                    ],                }            }        },    },     "sort" : {"id":"desc"}}

You can have a look to my solution in this notebook
I've created and example, populating a sample index and searching for desired apartments with this query

Comments answers:

1. Prefix: Since nested filter is performed setting path will be queried, prefix is no needed at all (at least in my tested version). And yes, you can add a field names start_date at document level or at another nested field
   

2. Apartment matches: Yes, it matches with 91 sample apartments, but since I did a search with default size parameter, only 10 are returned (I didn't specified its value, its default value). If you need to get ALL of them, use a scroll search

(notebook has been modified to clarify this points)

First of all, I think you must use the nested query.

I am not familiar with chewy-gem but the query would look something like:

:query => {  :nested: => {    :path: => "reservations",    :query => {      :bool => {        :must_not => [           {            :range => {:"reservations.start_date" => {:gte => "2017-02-10"}}          },           {            :range => {:"reservations.end_date" => {:lte => "2017-02-12"}}          }        ]      }    }  }}

But it might also not work as if there is a reservation in 2018, the fisrt bool query will be true (as the start date will be > 2017-02-10), therefore the appartment will not be returned, if I'm correct.

I would do something like:

:query => {  :nested: => {    :path: => "reservations",    :query => {      :bool => {        :must_not => [           {            :range => {:"reservations.start_date" => {:gte => "2017-02-10", :lte => "2017-02-12"}}          },           {            :range => {:"reservations.end_date" => {:gte => "2017-02-10", :lte => "2017-02-12"}}          }        ]      }    }  }}

which means no start date beetween the range you want, no end date beetween the range you want.

This is the query I came up with which is supposed to take into account all conditions, namely:

• either there are no reservations (1st top-level bool/should)
• or there are at least one reservation and the reservation start and end dates do not overlap with the requested dates.

Here, we're asking for free apartments between 2017-02-10 and 2017-02-12

{  "bool": {    "minimum_should_match": 1,    "should": [      {        "nested": {          "path": "reservations",          "query": {            "bool": {              "must_not": {                "exists": {                  "field": "reservations.start_date"                }              }            }          }        }      },      {        "bool": {          "must": [            {              "nested": {                "path": "reservations",                "query": {                  "bool": {                    "minimum_should_match": 1,                    "should": [                      {                        "range": {                          "reservations.start_date": {                            "gt": "2017-02-10"                          }                        }                      },                      {                        "range": {                          "reservations.end_date": {                            "lt": "2017-02-10"                          }                        }                      }                    ]                  }                }              }            },            {              "nested": {                "path": "reservations",                "query": {                  "bool": {                    "minimum_should_match": 1,                    "should": [                      {                        "range": {                          "reservations.start_date": {                            "gt": "2017-02-12"                          }                        }                      },                      {                        "range": {                          "reservations.end_date": {                            "lt": "2017-02-12"                          }                        }                      }                    ]                  }                }              }            }          ]        }      }    ]  }}