database - 如何正确存储和查询可用性数据

Question

我正在构建一个应用程序，允许人们标记他们何时可用/不可用，并允许其他人在此期间进行约会。但是，当有人进行搜索时，我无法找到一种存储和查询可用性的好方法。

例如:

• 主持人 A 在 10 月 1 日、3 日和 8 日上午 7 点至下午 4 点及以后有空11 日上午 9 点至 10 点和中午 12 点至下午 6 点。他们还有 10 月 1 日中午 12 点的(1 小时)约会
• 主持人 B 于 10 月有空1 日、4 日、6 日和 8 日上午 9 点至晚上 8 点
• 用户 C 正在搜索任何10 月 6 日至 8 日下午 4 点至 6 点有空，因此应该只能看到主持人 B
• 用户 D 正在搜索 10 月 9 日至 12 日上午 9 点至下午 1 点的任何空闲时间，所以应该只看到主机 A
• 用户 E 正在寻找 10 月 1 日与主持人 A 的空闲时间，因此应该能够在早上 7 点到上午 11 点以及下午 1 点到 4 点之间的任何时间预约 1 小时

存储和查询数据的有效方法是什么？

其他注意事项:

• 我将 elasticsearch 与 DynamoDB 结合使用来存储和查询数据
• 预订最多可以补上一年

Answer 1

我显然会利用 date_range data type (介绍 here )。您可以为每个主机和该主机的每个可用性创建一个文档(或者，如果您想要更细粒度的数据，则每小时一个可用性)。鉴于您的上述要求，它看起来像这样:

首先创建索引和映射:

PUT hosts
{
  "mappings": {
    "properties": {
      "name": {
        "type": "keyword"
      },
      "availability": {
        "type": "date_range"
      }
    }
  }
}

然后添加您的主机的可用性(我决定使用 GMT 时区，因此时间与您上面提到的时间 1:1 匹配):

PUT hosts/_doc/_bulk
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-01T07:00:00.000Z", "lte": "2019-10-01T12:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-01T13:00:00.000Z", "lte": "2019-10-01T16:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-03T07:00:00.000Z", "lte": "2019-10-03T16:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-08T07:00:00.000Z", "lte": "2019-10-08T16:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-11T09:00:00.000Z", "lte": "2019-10-11T10:00:00.000Z" }}
{"index": {}}
{ "name": "A", "availability": { "gte": "2019-10-11T12:00:00.000Z", "lte": "2019-10-11T18:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-01T09:00:00.000Z", "lte": "2019-10-01T20:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-04T09:00:00.000Z", "lte": "2019-10-04T20:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-05T09:00:00.000Z", "lte": "2019-10-05T20:00:00.000Z" }}
{"index": {}}
{ "name": "B", "availability": { "gte": "2019-10-06T09:00:00.000Z", "lte": "2019-10-06T20:00:00.000Z" }}

然后使用 range query 查询就很容易了:

User C is searching for any availability on Oct 6-8 from 4pm to 6pm, so should see only host B

POST hosts/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "availability": {
              "gte": "2019-10-06T16:00:00.000Z",
              "lte": "2019-10-06T18:00:00.000Z",
              "relation": "contains"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-07T16:00:00.000Z",
              "lte": "2019-10-07T18:00:00.000Z",
              "relation": "contains"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-08T16:00:00.000Z",
              "lte": "2019-10-08T18:00:00.000Z",
              "relation": "contains"
            }
          }
        }
      ]
    }
  }
}

结果:只有主机 B =>

  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "KzOggG0BzetAdRPqckxN",
    "_score" : 1.0,
    "_source" : {
      "name" : "B",
      "availability" : {
        "gte" : "2019-10-06T09:00:00.000Z",
        "lte" : "2019-10-06T20:00:00.000Z"
      }
    }
  }

User D is searching for any availability on Oct 9-12 from 9am to 1pm, so should see only host A

POST hosts/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "availability": {
              "gte": "2019-10-09T09:00:00.000Z",
              "lte": "2019-10-09T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-10T09:00:00.000Z",
              "lte": "2019-10-10T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-11T09:00:00.000Z",
              "lte": "2019-10-11T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-12T09:00:00.000Z",
              "lte": "2019-10-12T13:00:00.000Z",
              "relation": "intersects"
            }
          }
        }
      ]
    }
  }
}

结果:只有主机A =>

  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "JzOggG0BzetAdRPqckxN",
    "_score" : 1.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-11T12:00:00.000Z",
        "lte" : "2019-10-11T18:00:00.000Z"
      }
    }
  },
  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "JjOggG0BzetAdRPqckxN",
    "_score" : 1.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-11T09:00:00.000Z",
        "lte" : "2019-10-11T10:00:00.000Z"
      }
    }
  }

User E is searching for availability with Host A on October 1st, so should be able to make a 1 hour appointment at any from 7am to 11am, and from 1pm to 4pm

POST hosts/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "name": "A"
          }
        },
        {
          "range": {
            "availability": {
              "gte": "2019-10-01",
              "lt": "2019-10-02",
              "relation": "intersects"
            }
          }
        }
      ]
    }
  }
}

结果:主持人 A 早上 7 点到 12 点和下午 1 点到 4 点 =>

  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "IjOggG0BzetAdRPqckxN",
    "_score" : 0.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-01T07:00:00.000Z",
        "lte" : "2019-10-01T12:00:00.000Z"
      }
    }
  },
  {
    "_index" : "hosts",
    "_type" : "_doc",
    "_id" : "IzOggG0BzetAdRPqckxN",
    "_score" : 0.0,
    "_source" : {
      "name" : "A",
      "availability" : {
        "gte" : "2019-10-01T13:00:00.000Z",
        "lte" : "2019-10-01T16:00:00.000Z"
      }
    }
  }