How to get data from Firestore based on the person who is assigned to with many different ZIP Codes?

Question

Following is the code I use to pull the data out from the firestore. However, as part of the development of the app, my requirement is also changed. Now, what I need is to pull the orders only for the person who is assigned to serve a particular area which can be identified with the ZIP Code which every order will have. Presently, with my code, I can get the orders based on the status of the order and by the owner of the product who posted it, which is identified with the currently logged-in user. As per my new requirement, every ZIP Code is assigned to someone (a person can be assigned to provide service to many ZIP Codes.) and I need to show users only those orders that he is assigned to provide service at. I know how to pull the data if only one ZIP is assigned to a service provider. But in this case, I have no idea how to design my database to assign ZIP codes to the service providers and to get the data.

I thought of doing it using .whereIn like I did for the 'order Status', but there are two issues, one, that there can't be two .whereIns and, two, I do not want to hard code it. So I do not know how to do it, even if my thought was right.

I hope my question is clear.

        fun getOrderStatusList(fragment: OrdersByStatusFragment) {
        mFireStore.collection("orders")
            .whereIn(
                "order_status",
                listOf("Pending", "Order Received", "In Process", "Packed", "Shipped")
            )
            .whereEqualTo("product_owner_id", getCurrentUserID())
            .get()
            .addOnSuccessListener { document ->
                val list: ArrayList<OrderStatus> = ArrayList()

                for (i in document.documents) {

                    val orderStatus = i.toObject(OrderStatus::class.java)!!
                    orderStatus.id = i.id

                    list.add(orderStatus)
                }

                fragment.successOrderStatusList(list)

            }
            .addOnFailureListener {
                fragment.hideProgressDialog()
            }
    }

EDIT:

Zip Codes that are assigned to each users are available as in the screenshot of the database. Document names are same as the id of the current user ID which is also available inside the document with the field name 'id'.

I want to get only those 'orders' that match any Zip code which is assigned to the user. Every order contain a field called zipCode.

Answer 1

If it's possible to change the database schema, I would think of something like this:

Firestore-root
  |
  --- users
  |    |
  |    --- $uid
  |         |
  |         --- zipCodes: ["zipCodeOne", "zipCodeTwo"]
  |
  --- orders
       |
       --- $orderId
              |
              --- zipCode: "zipCodeOne"
              |
              --- status: "Pending"

Since a user can provide service to more than one ZIP code, I would add an array of zip codes to each and every user. To get the orders that correspond to a user, I would perform a query for each zip code a user has:

rootRef.collection("orders")
                    .whereEqualTo("zipCode", "zipCodeOne").get()

rootRef.collection("orders")
                    .whereEqualTo("zipCode", "zipCodeTwo").get()

Each statement from above will return a Task object, so you can pass all Task objects you have to the whenAllSuccess method, like explained my answer from the following post:

• Firestore - Merging two queries locally

Once I got the order, I can filter them on the client according to the status. However, if you need to get all the orders a user has, according to a specific status, you can also add another whereEqualTo() call like this:

rootRef.collection("orders")
                    .whereEqualTo("zipCode", "zipCodeOne")
                    .whereEqualTo("status", "Pending")
                    .get()

rootRef.collection("orders")
                    .whereEqualTo("zipCode", "zipCodeTwo")
                    .whereEqualTo("status", "Pending")
                    .get()

Answer 2

Since you cannot use multiple array queries, you should consider aggregating field values or separating the field types to boolean flags. such as FieldName:true pairs.

.whereEqualTo("Order_Recieved", "true")`

You could store the ZIP code in an array, while silly, this allows you to do an array contains-any

val citiesRef = db.collection("cities")

citiesRef.whereEqualTo("pending", "true")
.whereEqualTo("orderRecieved", "true")
.whereEqualTo("inProgress", "true")
.whereEqualTo("isPacked", "true")
.whereEqualTo("shipped", "true")
Order Received.whereArrayContainsAny("postcode", listOf("05723", "36236"))

Source: https://firebase.google.com/docs/firestore/query-data/queries#in_not-in_and_array-contains-any