Firebase 日期范围查询以获取具有 starDate 和 endDate 的文档

问题描述

我的 Firestore 数据库中有一系列任务，如下所示:

I have a collection of tasks on my Firestore database like this:

"tasks": {
    v8NC4GovVOPe21fhwtp0 : {
        id: "v8NC4GovVOPe21fhwtp0"
        status: 0
        dateFrom: 30 april 2019 at 00:00:00 UTC-3
        dateTo: 05 may 2019 at 00:00:00 UTC-3
        ....
    },
    v7NC4GaxVOPe48ftstp1 : {
        id:"v7NC4GaxVOPe48ftstp1"
        status: 0
        dateFrom: 29 april 2019 at 00:00:00 UTC-3
        dateTo: 02 may 2019 at 00:00:00 UTC-3
        ....
    }
}

文档有一个 dateFrom 字段，表示用户可以开始处理该任务的第一个日期和一个 dateTo 字段，表示用户拥有的最后一天完成该任务.

The document has a dateFrom field, which indicates the first date the user can start working on that task and an dateTo field, which indicates the last day the user have to finish that task.

我试图从 2019 年 4 月开始获取所有任务，这意味着 dateFrom 或 dateTo 属于本月/年的任务(在本例中，如果月份是 2019 年 4 月，它必须同时获取这两个任务).

I was trying to get all tasks from April 2019, which means the ones that either dateFrom or dateTo belongs to this month/year (in this example, if the month is April-2019, it must fetch both tasks).

在 Android 上，我得到了所选月份的第一个日期 (startDate) 和所选月份的最后一个日期 (endDate)，我正在尝试做某事喜欢:

On Android, I get the first date of the chosen month (startDate) and te last date from the chosen month (endDate) and I was trying to do something like:

val missionsCollection = FirebaseFirestore.getInstance()
        .collection("/users").document(uid).collection("tasks")
        .whereGreaterThanOrEqualTo("dateFrom", startDate).whereLessThanOrEqualTo("dateTo", endDate)

但我收到一条错误消息，提示 除 whereEqualTo() 之外的所有 where 过滤器都必须位于同一字段上.但是您在dateFrom"和dateTo"上有过滤器.

But I get an error saying that All where filters other than whereEqualTo() must be on the same field. But you have filters on 'dateFrom' and 'dateTo'.

我想了一些解决方法，但它们看起来都很糟糕，即使我找不到一个好的解决方案，我相信一定有一个.

I thought about some workarounds, but they all look bad and even though I could not find a nice solution, I'm sure there's gotta be one.

推荐答案

Firestore 允许链接多个where()"方法来创建更具体的查询，但仅限于同一字段.正如您可能在官方文档中看到的那样 查询限制:

Firestore allows to chain multiple "where()" methods to create more specific queries but only on the same field. As you can probably see in the official documentation regardin Query limitations:

Cloud Firestore 不支持以下类型的查询:

Cloud Firestore does not support the following types of queries:

• 在不同字段上使用范围过滤器进行查询，如上一节所述.

因此禁止对不同字段进行范围过滤.

So range filters on different fields are forbidden.

为了达到你想要的，你需要查询你的数据库两次，一次是使用调用来过滤数据:

To achieve what you want, you need to query your database twice, once to fiter data using a call to:

.whereGreaterThanOrEqualTo("dateFrom", startDate)

第二次使用调用:

.whereLessThanOrEqualTo("dateTo", endDate)

但很遗憾，您不能在同一个查询中使用它们.

But unfortunately you cannot use them in the same query.

当我们设计数据库架构时，基本上我们会为查询构建它.因此，除了上述解决方案(您可以过滤查询中的一个字段以及客户端代码中的另一个字段)之外，还有另一种方法可以将两个范围的值组合到一个字段中某种方式可以让您的用例使用单个字段.

When we are designing a database schema, basically we structure it for our queries. So beside the above solution in which you can filter on one field in the query, and on the other field in your client-side code, there is also another approach in which you can combine the values of the two range into a single field in some way that will allow your use-case with a single field.

到目前为止，我见过的最成功的例子之一是 Geohashes 中用于过滤纬度和经度的组合，正如 Frank van Puffelen 在以下视频中所解释的那样:

One of the most successful examples I've seen so far is the combination used in Geohashes to filter on latitude and longitude as explained by Frank van Puffelen in the following video:

• https://www.youtube.com/watch?v=mx1mMdHBi5Q

知道这两种解决方案在工作量上的区别，我建议使用第一种.

Knowing the difference in effort between these two solutions, I'd recommend using the first one.

还有第三种解决方案，您应该将 3 月至 4 月的所有任务添加到一个集合中.然后您可以使用以下命令查询这个新集合:

There is also a third solution, in which you should to add all tasks from Mar-Apr into a single collection. Then you could query this new collection with:

 db.collection("tasks-aps-mar")
    .whereGreaterThanOrEqualTo("day", 1)
    .whereLessThanOrEqualTo("day", 30);

更通用的解决方案是将每个月的任务存储在一个集合中，然后执行查询以获取与所需月份对应的所有任务.在您的用例中，您应该查询数据库以获取每个集合 tasks-mar-2019、tasks-apr-2019、tasks- 之一中的文档2019 年 5 月 等等.

Even a more general solution would be to store the tasks in a collection for each month, and then perform a query to get all the tasks that correspond with the desired month. In your use case, you should query your database to get the documents within one of each collection tasks-mar-2019, tasks-apr-2019, tasks-may-2019 and so on.

关于您的评论，使用数组对您毫无帮助，因为您不能使用范围间隔.

Regarding your comment, using arrays won't help you at all since you cannot use range intervals.

这篇关于Firebase 日期范围查询以获取具有 starDate 和 endDate 的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！