Block certain date ranges for pickup/dropoff

Question

I have items for rent. The User specifies a start_date and end_date. Every item has multiple blocked_periods also with start and end date.

Goal:

Query all available items. Lets say: 12.11., 13.11., 14.11., 15.11.

Blocked are 13. and 14.

The item should be available on 12. or 15. or from 12. until 15. but start and end date can't be on 13. and 14.

Current Index:

{
  "development_items" : {
    "aliases" : { },
    "mappings" : {
      "item" : {
        "properties" : {
          "blocked_periods" : {
            "type" : "nested",
            "properties" : {
              "end_date" : {
                "type" : "date",
                "format" : "yyyy-MM-dd"
              },
              "start_date" : {
                "type" : "date",
                "format" : "yyyy-MM-dd"
              }
            }
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "creation_date" : "1503327829680",
        "number_of_shards" : "5",
        "number_of_replicas" : "1",
        "uuid" : "9b9BhF-ITta2dlCKRLrnfA",
        "version" : {
          "created" : "2040499"
        }
      }
    },
    "warmers" : { }
  }
}

Current Query:

  {
    bool: {
      must_not: {
        nested: {
          path: 'blocked_periods',
          query: {
            bool: {
              should: [
                {
                  bool: {
                    must: [
                      {
                        range: {
                           'blocked_periods.start_date': {
                             lte: start_date
                           }
                        }
                      },
                      {
                        range: {
                           'blocked_periods.end_date': {
                             gte: end_date
                           }
                        }
                      }
                    ]
                  }
                }
              ]
            }
          }
        }
      }
    }
  }

I’m confused by your wording. Are you trying to get items where every blocked period for an item does not intersect with a given period? — Vincent, Nov 04 '18 at 14:12
the blocked periods are like for example holidays or a sunday where no one is around to handle pickup and dropoff for the item but you can rent the item during that period. — Oliver, Nov 04 '18 at 16:07
So you want items available for pickup on the given start date and available for drop off on the given end date? Are we to assume the item is available for pickup/drop off at any time not in a blocked period? — Vincent, Nov 04 '18 at 21:10

score 1 · Accepted Answer · answered Nov 05 '18 at 10:52

You WANT documents where the given start isn't in a blocked period and the given end isn't in a blocked period. Another way of saying this is you DONT want documents where the given start is in a blocked period or where the given end is in a blocked period (!A AND !B === !(A OR B)).

If we stick with your mapping and do this the nested way, heres how:

{
  "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "blocked_periods",
            "query": {
              "bool": {
                "should": [
                  {
                    "bool": {
                      "must": [
                        {
                          "range": {
                            "blocked_periods.start_date": {
                              "lte": "START" 
                            }
                          }
                        },
                        {
                          "range": {
                            "blocked_periods.end_date": {
                              "gte": "START"
                            }
                          }
                        }
                      ]
                    }
                  },
                  {
                    "bool": {
                      "must": [
                        {
                          "range": {
                            "blocked_periods.start_date": {
                              "lte": "END"
                            }
                          }
                        },
                        {
                          "range": {
                            "blocked_periods.end_date": {
                              "gte": "END"
                            }
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

However if you can handle changing the names of your fields from start_date/end_date to gte/lte, I think you'll find the date_range approach preferable.

PUT my_index
{
  "mappings": {
    "item": {
      "properties": {
        "blocked_periods": {
          "type": "date_range",
          "format": "yyyy-MM-dd"
        }
      }
    }
  }
}

POST my_index/item/1
{
  "blocked_periods": [
    {
      "gte": "2020-1-10",
      "lte": "2020-1-15"
    },
    {
      "gte": "2020-1-17",
      "lte": "2020-1-25"
    }
  ]
}

GET my_index/_search
{
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "blocked_periods": "START"
          }
        },
        {
          "term": {
            "blocked_periods": "END"
          }
        }
      ]
    }
  }
}

Block certain date ranges for pickup/dropoff

1 Answers1