Firebase日期范围查询以获取具有starDate和endDate的文档 [英] Firebase date range query to fetch documents that have starDate and 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)
但是我收到一个错误消息，说是All where filters other than whereEqualTo() must be on the same field. But you have filters on 'dateFrom' and '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:
了解这两种解决方案在工作量上的差异后，建议您使用第一种解决方案.
Knowing the difference in effort between these two solutions, I'd recommend using the first one.
还有第三种解决方案，您应该将Mar-Apr中的所有任务添加到单个集合中.然后，您可以使用以下命令查询此新收藏集:
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-may-2019等中的文档.
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的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
