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.
I want to fetch all available apartments which have no reservation at the given date or no reservations at all.
Unfortunately returns all apartments:
:query => {
:bool => {
:must_not => [
{
:range => {:"reservations.start_date" => {:gte => "2017-02-10"}}
},
{
:range => {:"reservations.end_date" => {:lte => "2017-02-12"}}
}
]
}
}
{
"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" : { }
}
}
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:
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
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)