Filtering the Firestore documents with more than one query

Question

I am trying to access the list of doctors that belong to 'someDepartment' and work for 'someHospital' from the given data in hospitalnames and docnames collection in Firestore.

hospitalnames collection:

docnames collection :

For example, doctors working in SJMC hospital in Gynecology department will be ['Dr. Jade Orman', 'Dr. Lia Reimers, 'Dr. Smith Johanson']

My ultimate goal is to show this list inside a DropDownButton.

This was my implementation till now [this returns null for some reason]

 List<String>? doctors = [];
 Future<dynamic> _getDoctors() async {
   var doctorNames = await FirebaseFirestore.instance
    .collection('docnames')
    .where('hospital', arrayContains: _hospital) // `_hospital` is set from hospital dropdown
    .where("department", isEqualTo: _department) // `_department` is set from department dropdown
    .get()
    .then((value) => value.docs.map((e) => e.data()['name']).toList());
     doctors = doctorNames.cast<String>();
    return doctorNames;
  }

I also tried this approach [returns null too!]

 List<String>? doctors = [];
 Future<dynamic> _getDoctors() async {
   var doctorNames = await FirebaseFirestore.instance
    .collection('docnames')
    .orderby(_hospital, descending: false) // `_hospital` is set from hospital dropdown
    .where("department", arrayContains: _department) // `_department` is set from department dropdown
    .get()
    .then((value) => value.docs.map((e) => e.data()['name']).toList());
     doctors = doctorNames.cast<String>();
    return doctorNames;
  }

This is the error I am getting:

My app has 3 DropDownButton:

• the first one shows the list of hospitals directly,
• the second one shows the departments present on the Hospital selected on the previous `DropDFirestore, I tried it but it didn't work out.

Please point out anything I can try to do better for implementing this kind of feature [Dynamic Dependent DropDrownButton list] (Successive dropdown depends on the previous dropdown items selected)

Answer 1

The first param in .where() is the field name and not the value you are looking for. So .where(_hospital, isEqualTo: 'SJMC') will be looking for a field name that is equal to value of _hospital.

Then in your documents, "department" seems to be a string and "hospital" seems to be an array. So ideally you would be using arrayContains on hospital field and isEqualTo on department but it's the other way at the moment. Try refactoring the code as shown below:

 List<String>? doctors = [];

 // Can add these 2 for testing
 print(_hospital);
 print(_department);

 Future<dynamic> _getDoctors() async {
   var doctorNames = await FirebaseFirestore.instance
    .collection('docnames')
    .where("hospital", arrayContains: _hospital) // `_hospital` is set from hospital dropdown
    .where("department", isEqualTo: _department) // `_department` is set from department dropdown
    .get()
    .then((value) => value.docs.map((e) => e.data()['name']).toList());
     doctors = doctorNames.cast<String>();
    return doctorNames;
  }