node.js - 使用单个命令进行查询和插入

Question

我有以下文件:

{
  "_id": "538584aad48c6cdc3f07a2b3",
  "startTime": "2014-06-12T21:30:00.000Z",
  "endTime": "2014-06-12T22:00:00.000Z",
},
{
  "_id": "538584b1d48c6cdc3f07a2b4",
  "startTime": "2014-06-12T22:30:00.000Z",
  "endTime": "2014-06-12T23:00:00.000Z",
}

它们都有 startTime 和 endTime 值。我需要保持集合中没有两个日期范围重叠的一致性。

假设我添加具有以下日期的以下文档:

db.collection.insert({
                      "startTime": "2014-06-12T19:30:00.000Z",
                      "endTime": "2014-06-12T21:00:00.000Z"
                     });

此日期范围插入应该会失败，因为它与现有间隔重叠。

我的问题是:

• 如何检查日期范围是否重叠？
• 如何通过单个查询进行检查和插入？

编辑:为了防止重复，我在那里询问并开始赏金。我需要使用单个查询进行更新操作，如下所述:How to query and update document by using single query?

Answer 1

查询并不像乍一看那么复杂 - 查找与给定范围“重叠”的所有文档的查询是:

db.test.find( { "startTime" : { "$lt" : new_end_time }, 
                "endTime"   : { "$gt": new_start_time } 
            } 
)

这将匹配开始日期早于结束日期且结束日期晚于开始时间的任何文档。如果将范围可视化为线上的点:

-----|*********|----------|****|-----------|******||********|---    s1         e1         s2   e2         s3     e3s4       e4

the sX-eX pairs represent existing ranges. If you take a new s5-e5 you can see that if we eliminate pairs that start after our end date (they can't overlap us) and then we eliminate all pairs that end before our start date, if we have nothing left, then we are good to insert.

That condition would be does a union of all documents with end date $lte our start and those with start date $gte ours include all documents already in collection. Our query flips this around to make sure that no documents satisfy the opposite of this condition.

On the performance front, it's unfortunate that you are storing your dates as strings only. If you stored them as timestamps (or any number, really) you could make this query utilize indexes better. As it is, for performance you would want to have an index on { "startTime":1, "endTime":1 }.

It's simple to find whether the range you want to insert overlaps any existing ranges, but to your second question:

How to check and insert with a single query?

There is no way proper way to do it with an inserts since they do not take a query (i.e. they are not conditional).

However, you can use an updates with upsert condition. It can insert if the condition doesn't match anything, but if it does match, it will try to update the matched document!

So the trick you would use is make the update a noop, and set the fields you need on upsert only. Since 2.4 there is a $setOnInsert operator to update. The full thing would look something like this:

db.test.update( 
   { startTime: { "$lt" : new_end_time }, "endTime" : { "$gt": new_start_time } }, 
   { $setOnInsert:{ startTime:new_start_time, endTime: new_end_time}},
   {upsert:1}
)
WriteResult({
"nMatched" : 0,
"nUpserted" : 1,
"nModified" : 0,
"_id" : ObjectId("538e0f6e7110dddea4383938")
})
db.test.update(
   { startTime:{ "$lt" : new_end_time }, "endTime" : { "$gt": new_start_time } },
   { $setOnInsert:{ startTime:new_start_time, endTime: new_end_time}},
   {upsert:1}
)
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 0 })

我刚刚执行了两次相同的“更新” - 第一次，没有重叠文档，因此更新执行了“更新插入”，您可以在它返回的 WriteResult 中看到这一点。

当我第二次运行它时，它会重叠(当然，它本身)，因此它尝试更新匹配的文档，但注意到没有任何工作要做。可以看到返回的nMatched为1，但没有插入或修改任何内容。