Get available apartments query

Question

Overview

I have apartments which have reservations. My index has the reservations as nested fields with date fields for start_date and end_date.

I'm using the chewy ruby gem - but this doesn't matter at this time i think. Just need to get my query right.

Goal

I want to fetch all available apartments which have no reservation at the given date or no reservations at all.

Current query

Unfortunately returns all apartments:

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

Index Settings

{
  "apartments" : {
    "aliases" : { },
    "mappings" : {
      "apartment" : {
        "properties" : {
          "city" : {
            "type" : "string"
          },
          "coordinates" : {
            "type" : "geo_point"
          },
          "email" : {
            "type" : "string"
          },
          "reservations" : {
            "type" : "nested",
            "properties" : {
              "end_date" : {
                "type" : "date",
                "format" : "yyyy-MM-dd"
              },
              "start_date" : {
                "type" : "date",
                "format" : "yyyy-MM-dd"
              }
            }
          },
          "street" : {
            "type" : "string"
          },
          "zip" : {
            "type" : "string"
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "creation_date" : "1487289727161",
        "number_of_shards" : "5",
        "number_of_replicas" : "1",
        "uuid" : "-rM79OUvQ3qkkLJmQCsoCg",
        "version" : {
          "created" : "2040499"
        }
      }
    },
    "warmers" : { }
  }
}

Answer 1

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)

Answer 2

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.

Answer 3

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"
                          }
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    ]
  }
}