Database count of values

Question

I my database structure userDatabase/userID/Customers I have two customers. For example path:

usersDatabase
    g8voYf1yoChnpmhkoPgtmO4FQT62 - (uid)
        Customers
            Tom Smith (customer with custom ID)
                -LDFZw1tca8KOrnqyyWH - (auto id of customer child)
                    Status of Service: "Open service"
            Ben Thomas (customer with custom ID)
                -LDFZw1tca8KOjgoenBN - (auto id of customer child)
                    Status of Service: "Open service"

Is possible to fetch count of value "Open service" form all customers in my database? Now I only know, how to print this value for each customers...

My code to get value from Database:

let userID = Auth.auth().currentUser?.uid
    let usersDatabaseRef = Database.database().reference().child("usersDatabase").child(userID!).child("Customers")
    usersDatabaseRef.observe(.value, with: { snapshot in
        var totalCustomerCount = 0
        for child in snapshot.children {
            let childSnap = child as! DataSnapshot
            let childrenRef = childSnap
            totalCustomerCount += Int(childrenRef.childrenCount)
            print("user \(childSnap.key) has \(childrenRef.childrenCount) customers")

            let userCustomerSnap = childSnap
            for customer in userCustomerSnap.children.allObjects as! [DataSnapshot] {
                let customerSnap = customer
                let dict = customerSnap.value as! [String: Any]

                let stat = dict["Status of Service"] as! String

                let myStatistic = PrintModel(status: stat)
                self.statistic.append(myStatistic)
                print("Statistic: \(String(describing: myStatistic.status))")
            }
        }
        print("... and there are \(totalCustomerCount) total customers")
    })

For example my log now show:

1. user Tom Smith has 1 customers
2. Statistic: Optional("Open service")
3. user Ben Thomas has 1 customers
4. Statistic: Optional("Open service")

but I want to show:

1. Statistic: 2

Answer 1

When structuring Firebase data, the structure is generally based on what you want to get out of it. In this case the data you want is too deep within the posted structure to be useful. So, changing the structure will make this a snap.

Separate your data into their own nodes, denormalizing the data. Like this

users
   uid_0
     //some  info about this user
     open_service_calls
          auto_id_0: true
          auto_id_1: true
   uid_1
     //info about this user

customers
    customer_id_0
      customer_name: "Tom Smith"
      open_service_calls
          auto_id_0: true
    customer_id_1
      customer_name: "Ben Thomas"
      open_service_calls
          auto_id_1: true

service_calls
   auto_id_0
      customer_id: customer_id_0
      user_id: "uid_0"
      status_of_service: "Open service"
   auto_id_1
      customer_id: customer_id_1
      user_id: "uid_0"
      status_of_service: "Open service"

That allows for queries by customer, user, and to address this question, an easy count of all Open Service in the database for all users and customers; query the service_calls/status_of_service for "Open service"

It also allows you to quickly access all service calls, open or closed for any user, any customer and even just open service calls for a users's customer.

Additional nodes would offer further query flexibility - storing the user id within the customer node would allow a super easy query to retrieve all of the customers for a specific user even if they have no service calls; it all depends on what you want to get out of Firebase.

--- Old answer is below ---

Per my comment to the original question, this answer involves using a Deep Query to query a child of child node of customers. The idea here is that within the user node, there's a Customers node where each child key is a customer name and the value of that node is a key: value pair of the string "serviceID" and then a value which may contain other child nodes about the service.

The important part of a Deep Query is to keep the child keys you are query'ing named consistently - in this case we use the key 'serviceID' so the query can properly resolve the path and then any of the child nodes of that can be queried: status_of_service, perhaps a time stamp or even the service location

The initial structure before it was changed was

Customers
  Tom Cruise
      serviceID
         status_of_service: "Open service"
         //timestamp of service?
         //location of service?
         //other data about service we may need to query?
  Ben Smith
      serviceID:
         status_of_service: "Open service"

Note that for this answer self.ref = my firebase so Customers is a direct child of that path.

let ref = self.ref.child("Customers")
let query = ref.queryOrdered(byChild: "serviceID/status_of_service")
               .queryEqual(toValue: "Open service")
query.observeSingleEvent(of: .value, with: { (snapshot) in 
    for child in snapshot.children {
        let snap = child as! DataSnapshot
        print(snap)
    }
})

the output is two customer nodes

   tom_smith
      serviceID
        status_of_service: "Open service"
   another_customer
      serviceID
        status_of_service: "Open service"