Flutter - executing a query on a query with firebase

Question

I have done some research on the firebase website, but did not find the right solution. I have understood that with Firebase, we can not have several where in a query because 'and' is not handled by Firebase. After some research, I found that we can do a query on a query. This is the solution for what I am trying to do. Here, I want to get all the tasks without empty field in Start and Due date.

Below you will find the code. I am using a stream. When I will have executed the query on a query, I want to use the result to populate a SfCalendar widget. Thank you

First test

Stream<QuerySnapshot> getAllTasksStreamSnapShots (BuildContext context) async*{

    yield* FirebaseFirestore.instance
        .collection('Users')
        .doc(FirebaseAuth.instance.currentUser!.uid)
        .collection('allTasks')
        .where('task_Start_Date', isNotEqualTo: '')
       // .where('task_Due_Date', isNotEqualTo:  '') 
        .snapshots();

    /*final myQuery1 = FirebaseFirestore.instance
        .collection('Users')
        .doc(FirebaseAuth.instance.currentUser!.uid)
        .collection('allTasks')
        .where('task_Start_Date', isNotEqualTo: '')
    // .where('task_Due_Date', isNotEqualTo:  '')
        .snapshots();*/
  }
}

If I add the second where clause, I am getting this error

Dart Unhandled Exception: 'package:cloud_firestore/src/query.dart': Failed assertion: line 720 pos 16: '!hasNotEqualTo': You cannot use '!=' filters more than once., stack trace: #0 _AssertionError._doThrowNew (dart:core-patch/errors_patch.dart:51:61)

Stream<QuerySnapshot> getAllTasksStreamSnapShots (BuildContext context) async*{

   final myQuery1 = FirebaseFirestore.instance
        .collection('Users')
        .doc(FirebaseAuth.instance.currentUser!.uid)
        .collection('allTasks')
        .where('task_Start_Date', isNotEqualTo: '')
    // .where('task_Due_Date', isNotEqualTo:  '')
        .snapshots();

    var myQuery2 = myQuery1
        .where('task_Due_Date', isNotEqualTo:  '')
        .get();
}

with this one, I am getting several errors.

The argument type 'String' can't be assigned to the parameter type 'bool Function(QuerySnapshot<Map<String, dynamic>>)'.

The named parameter 'isNotEqualTo' isn't defined.

The method 'get' isn't defined for the type 'Stream'.

Future getDatesNotEmpty() async {

    var data = FirebaseFirestore.instance
        .collection('Users')
        .doc(FirebaseAuth.instance.currentUser!.uid)
        .collection('allTasks')
        .where('task_Start_Date', isNotEqualTo: '')
        .get();

    var data2 = data.where('task_Due_Date', isNotEqualTo: '')
  }

With this third option, the error is:

The method 'where' isn't defined for the type 'Future'.

Here is the Fourth test


final myQuery1 = FirebaseFirestore.instance
        .collection('Users')
        .doc(FirebaseAuth.instance.currentUser!.uid)
        .collection('allTasks')
        .where('task_Start_Date', isNotEqualTo: '');

    var myQuery2 = myQuery1.firestore.collection('Users')
        .doc(FirebaseAuth.instance.currentUser!.uid)
        .collection('allTasks')
        .where('task_End_Date', isNotEqualTo: '');

    print('Size record');
    print(myQuery2.snapshots().length);

on the fourth test, when I try to print the number of record, I am getting this 'Instance of 'Future' instead of a number.

You can create [compound queries](https://firebase.google.com/docs/firestore/query-data/queries#compound_queries). You have to add [composite index](https://firebase.google.com/docs/firestore/query-data/indexing) to do so. — Peter Koltai, Aug 26 '22 at 20:22
What isn't working about the code you shared? So: when you step through this code line by line in de a debugger, which is the **first** line that doesn't do what you expect it to do? Is there an error message? — Frank van Puffelen, Aug 27 '22 at 07:05
Thank you. I have done that, but the result I am getting are not good. In a record, if task_Start_Date is empty and task_Due_Date is not empty, my application display the record. I only want to see records where the two dates are not empty. I hope it is clear. — Laurent Thomas, Aug 27 '22 at 12:21
@Frank: I have updated my previous code and display the errors. If I have three records. In one, start_date='' and Due_Date = '2022-08-08", second record 'Start_Date'='2022-06-07 and Due_Date= '2022-10-15, third record Start_Date =2022-08-09 and due_Date = ''. The query is displaying the three record. It should only display the second record, where both Start_Date and Due_Date are not empty. – — Laurent Thomas, Aug 27 '22 at 12:45
For clarity, the question states - *we can not have several where in a query because 'and' is not handled by Firebase* - which by itself is not accurate. Of course Firestore can do `and` queries. The problem in the question is they are **inequality** statements, which *must be on the same field*. If the fields you want to query are going to not contain any data, can't you just remove them? Then getting documents that have data in those fields will always return documents where those fields are populated. — Jay, Aug 28 '22 at 13:23
@Jay: Thank you. Those two fields are important. The user can use them or not. He can decide to record a start date & end date, or just the start date and or just the end date. I must anticipate and be able to deal with all the possibilities. All the documents whit both the start date and end date will be displayed in a calendar view. I hope this help to clarify what I am trying to do. — Laurent Thomas, Aug 29 '22 at 14:49
Remember, Firestore is a NoSQL database, not SQL; fields are not required and will be created automatically when the data exists. If the users populates a start and end date, when that document is written, the fields will be created. If they only enter a startDate or endDate then only that field will be written. Bases on *All the documents whit both the start date and end date will be displayed* the query will filter that for you - if your query is `startDate > x and endDate < y` if there is no end date field, that document won't be returned - meeting your criteria. Just a thought. — Jay, Aug 29 '22 at 19:35
@Jay: I agree. In my code, I have a function. If the user do not type any date start or end, I will record null in the document. I am doing this to avoid error on query because the field does not exist. I have found FireSQL. Please, can you tell me what do you think of this? Thank you — Laurent Thomas, Aug 29 '22 at 19:48
Not familiar with it and honestly, it's likely not needed. Firestore is very comprehensive for most use cases. — Jay, Aug 29 '22 at 20:46

Peter Obiechina · Answer 1 · 2022-08-28T08:51:35.883

In the first test:

You can not use '!=' filters more than once as the error tells you.

A way around that would be to create another field called has_start_and_end_date. This field should be set to true if start_date and end_date are not empty. Then, you can then call

Stream<QuerySnapshot> getAllTasksStreamSnapShots() {
  return FirebaseFirestore.instance
      .collection('Users')
      .doc(FirebaseAuth.instance.currentUser!.uid)
      .collection('allTasks')
      .where('has_start_and_end_date', isEqualTo: true)
      .snapshots();
}

NB: This query returns every task document where the has_start_and_end_date field exists with a value equal to true.

You can update the has_start_and_end_date field using cloud functions or whereever you set the value of start_date and end_date in your code.

In the second test:

snapshots() would return type a stream (Stream<QuerySnapshot>) and it has no where firestore filter method (it has the list method where).

In the second test:

get() would return type a future (Future<QuerySnapshot>) and it has no where method.

EDIT: You can also get the list of tasks from firebase and filter on the user's device.

Future<List<Map<String, dynamic>>> getAllTasksStreamSnapShots() async {
  final taskDocs = await FirebaseFirestore.instance
      .collection('Users')
      .doc(FirebaseAuth.instance.currentUser!.uid)
      .collection('allTasks')
      .where('task_Start_Date', isNotEqualTo: '')
      .get();
  final tasks = taskDocs.docs.map((e) => e.data()).toList();
  tasks.removeWhere((e) => (e['task_End_Date'] as String).isEmpty);
  return tasks;
}

and you can utilise this like this: final tasks = await getAllTasksStreamSnapShots();

NB: This will incur extra costs (For reading tasks where task_End_Date is empty)

Thank you Peter. It is a good idea to add a new field where I store has_Start_and_end_date. My only concern is the cost of firebase. This would be an other read and write field. This would increase the cost I will have to pay to google. Ideally, it would for me to do a QuerySnapshot. Then to store the result somewhere, in a list or a map maybe. And then, to filter this based on non empty fields start and due date. But I do not know how to code this. — Laurent Thomas, Aug 27 '22 at 18:56
I have updated the code to show how you can do that. NB: This will incur extra reading costs (for tasks where `task_End_Date` is empty) — Peter Obiechina, Aug 28 '22 at 08:53

Flutter - executing a query on a query with firebase

1 Answers1