使用单个命令查询和插入 [英] Query and Insert with a single command

问题描述

我有以下文件：

{
  "_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值。我需要保持一致性，即收集中没有两个日期跨度重叠。

All of them have startTime and endTime value. I need to maintain consistency that no two date spans in the collection overlap.

假设我添加以下日期文件：

Let's say if I add the following document with the following dates:

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

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

This date span insert should fail because it overlaps with an existing interval.

我的问题是：

• 如何检查日期跨度重叠？ / li>
  
• 如何使用单个查询检查和插入？

编辑：防止重复我问那里，开始一个赏金。我需要使用如下所述的单个查询进行更新操作：如何使用单个查询查询和更新文档？

to prevent duplicate I ask there and start a bounty. I need to make update operation by using single query as described here: How to query and update document by using single query?

推荐答案

查询不是首先看起来很复杂 - 查询找到所有文档的重叠范围是：

The query is not as complicated as it may look at first - the query to find all documents which "overlap" the range you are given is:

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

这将匹配任何文档的起始日期早于我们的结束日期和结束日期大于我们的开始时间。如果您将范围视为一行上的点数：

This will match any document with starting date earlier than our end date and end date greater than our start time. If you visualize the ranges as being points on a line:

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

sX-eX对表示现有范围。如果您使用新的s5-e5，您可以看到，如果我们消除了在我们的结束日期之后开始的对（它们不能与我们重叠），然后我们消除在开始日期之前结束的所有对，如果我们没有剩下什么，那么我们很好的插入。

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.

该条件将是结束日期 $ lte 的所有文档的联合，我们的开始和开始日期我们包括已经收集的所有文件。 $ gte 我们的查询翻转这个周围，以确保没有文件满足这种情况的相反。

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.

在性能方面，不幸的是您只将日期存储为字符串。如果您将它们存储为时间戳（或任何数字，真的），您可以使此查询更好地利用索引。就是这样，为了表现，你需要在 {startTime：1，endTime：1} 上有一个索引。

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).

但是，您可以使用具有upsert条件的更新。它可以插入，如果条件不匹配任何东西，但如果它匹配，它将尝试更新匹配的文档！

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!

所以你会使用的技巧是更新noop，并且仅在upsert上设置所需的字段。由于2.4有一个 $ setOnInsert 操作符来更新。完整的东西看起来像这样：

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 })

我刚刚做了相同的更新两次 - 第一次，没有重叠文件，所以更新执行一个upsert，您可以在 WriteResult 中看到它的返回值。

I just did the same "update" twice - the first time, there was no overlap document(s) so the update performed an "upsert" which you can see in the WriteResult it returned.

当我第二次运行它，它将重叠（本身，课程），所以它试图更新匹配的文档，但注意到没有工作要做。您可以看到返回的已经是1，但没有插入或修改。

When I ran it a second time, it would overlap (itself, of course) so it tried to update the matched document, but noticed there was no work to do. You can see the returned nMatched is 1 but nothing was inserted or modified.

这篇关于使用单个命令查询和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！