2

I have a query that I would like to order by a start date and filter by end date.

return FirebaseFirestore.instance
        .collection("content")
        .where("active", isEqualTo: true)
        .where("end", isGreaterThanOrEqualTo: DateTime.now())
        .orderBy("start", descending: true)
        .snapshots();

Error:

The initial orderBy() field '[[FieldPath([start]), true]][0][0]' has to be the same as the where() field parameter 'FieldPath([end])' when an inequality operator is invoked.

Doug Stevenson
  • 297,357
  • 32
  • 422
  • 441
William
  • 884
  • 2
  • 12
  • 23

2 Answers2

3

If you include a filter with a range comparison (<, <=, >, >=), your first ordering must be on the same field

https://firebase.google.com/docs/firestore/query-data/order-limit-data#limitations

So you should go with

return FirebaseFirestore.instance
        .collection("content")
        .where("active", isEqualTo: true)
        .where("end", isGreaterThanOrEqualTo: DateTime.now())
        .orderBy("end", descending: true)
        .orderBy("start", descending:true)
        .snapshots();
stacktrace2234
  • 1,172
  • 1
  • 12
  • 22
  • Thank you for answering. So if I want them sorted in ascending order by start ... how would that be? Right now they're not ordered correctly. I tried your code and also changed `.orderBy("start", descending:false)`, created the indexes, but still not ordered correctly. – William Aug 25 '21 at 06:24
  • Yeah ... not working. Tried multiple orderBy but still not ordered in `ascending` order by `start`. – William Aug 25 '21 at 06:39
  • Well, you should use the field `start` in a `where` clause so you can `orderBy` using only the `start`. You could search for `start <= DateTime.now() && end >= DateTime.now()`. Guess all your starts are before the end so then you can `orderBy start` – stacktrace2234 Aug 25 '21 at 06:41
  • That would mean that it will only show started events. And I don't want that. I need to show upcoming events up to the end date. Some events span across multiple days/weeks/months. So an event has to be viewable until its end date. – William Aug 25 '21 at 06:58
  • 1
    Well, either you find out how can you work with the `end` and `start` parameters so you can use the `start` as an `orderBy` parameter, or you save the result in a list and throw out of that what you don't need... – stacktrace2234 Aug 25 '21 at 07:12
  • Yeah ... I think I need to do that. I just thought that it can be done when making the query. – William Aug 25 '21 at 09:22
1

You are getting the following error:

The initial orderBy() field '[[FieldPath([start]), true]][0][0]' has to be the same as the where() field parameter 'FieldPath([end])' when an inequality operator is invoked.

Because there is no way you can get data from Firestore without first sorting on the end field when using an inequality operator. So you need to reorder the results in your application code. So after calling:

.where("end", isGreaterThanOrEqualTo: DateTime.now())

It's mandatory to call:

.orderBy("end", descending:false)

So your query should look like this:

return FirebaseFirestore.instance
    .collection("content")
    .where("active", isEqualTo: true)
    .where("end", isGreaterThanOrEqualTo: DateTime.now())
    .orderBy("end", descending:false)
    .orderBy("start", descending:true)
    .snapshots();

Don't also forget to create an index in order to make this query work.

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193