Firestore Query with search in document and sub-collection

Question

I have an employee document (name, designation, ...) and a sub-collection containing all the shifts (documents of the sub-collection) assigned to this employee.

When assigning a new shift, I want to retrieve only the employees that have a particular designation, meaning they are skilled to perform this shift.

Furthermore, I should search within the sub-collection and fetch only the employees that do not have a document for the same date of the shift to assign.

At the moment I only know how to retrieve the employees that have a particular designation.

What I am not able to implement is the part where I only get the skilled employees that are free on the day of the shift to assign.

 @override
  Stream<List<Employee>> availableEmployeesForGivenDesignation(String designation, DateTime statusDate) {
    return _employeeCollection
        .where('designation', isEqualTo: designation)
        .snapshots()
        .map((snapshot) {
         //todo search in the sub-collection
          ...
        });
  }

Frank van Puffelen · Accepted Answer · 2020-05-10T18:54:36.550

2

Firestore queries only work across a single type of collection. There is no way to search both employees and their shifts in a single query, you will need at least two queries for that.

To allow your use-case with a single query, you'll have to duplicate data. You had two options there:

Duplicate the necessary shift data into the employee document, and then query only the employee collection.
Duplicate the necessary employee data into their shift documents, and then perform a collection group query across all shifts collections.

Given what you described the second option sounds most promising. It'd look something like this:

db.collectionGroup("shifts")
  .where('designation', isEqualTo: designation)
  .where(...your other condition...)
  .snapshots()
  .map((snapshot) {
    print(snapshot.reference.parent().parent().documentID);
  })

You'd then use that document ID to look up the employee document.

edited May 10 '20 at 18:54

answered May 10 '20 at 14:38

Frank van Puffelen

565,676
79
828
807

So you mean, pass some employee data as fields to the shift document found in a respectable sub-collection. I still do not know how to get only the employees with no shifts for a given date. Remember, I only want to get the selection of available employees. – Georgios May 10 '20 at 17:07
1

You can't search for documents **without** a value. So you might want to consider inserting all shifts and then keep a "is booked" field in there. The only alternatives I can think of are to: 1) keep a map of available in the employee document (option 1 in my answer), 2) load each employee and query their shifts separately subcollection separately to see if there's any results. – Frank van Puffelen May 10 '20 at 18:57
This is great stuff. Which option would be more cost-efficient, if I would keep within the employee document up to two weeks of availability data (map dates as keys and bool as value)? – Georgios May 10 '20 at 19:01
That sounds like a separate question to me. But cost is fairly simple: do a napkin calculation of how many documents are read, how many are written, and how much data is read, and put them into the [pricing calculator](https://firebase.google.com/pricing#blaze-calculator). – Frank van Puffelen May 10 '20 at 19:39

Firestore Query with search in document and sub-collection

1 Answers1

Linked