How to apply multiple where clauses with different depth levels in data in firebase firestore?

Question

I am trying to fetch data from Firebase Firestore with multiple where conditions. The problem is the where conditions are to apply at different levels. `

db.collection("users")
            .whereEqualTo("sex", oppositeSex)
            .whereNotEqualTo("connections/likedBy", true) // this is the additional condition I need
            .whereNotEqualTo("connections/dislikedBy", true) // this is the additional condition I need
            .get()
            .addOnCompleteListener { task ->`.....

This is the database structure

In these queries what I am trying to achieve is a way to be able to extend the path(db reference) to the child collections. I have used "/" just to show what needs to be done. How can this be achieved?

UPDATE:

    private fun retrieveOppositeSexUsers() {

    try {
        var oppositeSex = if (currentUserSex == "Male") "Female" else "Male"

        db.collection("users")
            .whereEqualTo("sex", oppositeSex)
            .get()
            .addOnCompleteListener { task ->
                if (task.isSuccessful) {
                    arrlstOppositeSexUsers.clear()
                    try {
                        task.result.apply {
                            db.collection("users").document().collection("connections")
                                .whereNotEqualTo(strCurrentUserID, true).get()
                                .addOnCompleteListener { task ->
                                    for (document in task.result!!) {
                                        val userID = document.id
                                        val name = document.data["username"].toString()
                                        val profileImageURL =
                                            document.data["profileImageURL"].toString()
                                        val user = User(userID, name, profileImageURL)

                                        arrlstOppositeSexUsers.add(user)
                                        flingContainer.adapter = userAdpater
                                        userAdpater!!.notifyDataSetChanged()
                                        Log.d(TAG, document.id + " => " + document.data)
                                    }
                                }
                        }
                    } catch (e: Exception) {
                        e.printStackTrace()
                    }

                } else {
                    Log.d(TAG, "Error getting documents.", task.exception)
                }
            }

    } catch (e: Exception) {
        e.printStackTrace()
    }

}

score 1 · Answer 1 · answered Jun 18 '21 at 07:56

1

How to apply multiple where clauses with different depth levels in data in Firebase Firestore?

You cannot do that. Queries in Firestore are shallow, it can only get documents from the collection that the query is run against. There is no way you can get documents from a top-level collection and a sub-collection in a single query. Firestore doesn't support queries across different collections in one go. A single query may only use properties of documents in a single collection.

In your particular use case, there are two solutions I can think of. The first one would be to add in each document under "connections" collection, the fields you are interested in, for example, "sex" and use a query that looks like this:

FirebaseAuth.getInstance().currentUser?.apply {
    db.collection("users").document(uid).collection("connections")
        .whereEqualTo("sex", oppositeSex)
        .whereEqualTo(uid, true)
}

If you cannot change the database schema, then the second option that you have is to perform two separate queries. The first one would be:

db.collection("users")
        .whereEqualTo("sex", oppositeSex)
        .get()
        .addOnCompleteListener {/* ... /*}

Once you get the results, perform the second query that should look like this:

FirebaseAuth.getInstance().currentUser?.apply {
    db.collection("users").document(uid).collection("connections")
        .whereEqualTo(uid, true)
}

If you need only two queries, then you can use Tasks#whenAllSuccess() method as explained in my answer from the following post:

Firestore - Merging two queries locally

Please note that in both queries, I have used a call to .whereEqualTo(uid, true), and this is because, in your "likedBy" document, you have a single property which is actually the UID. This property holds the value of true. When you perform a Query against a Firestore collection, there is no need to specify any documents. However, if you query a sub-collection, the document that is between the first collection and the sub-collection should be specified in the reference.

A call to:

FirebaseAuth.getInstance().currentUser?.apply {
    db.collection("users").document(uid).collection("connections")
        .whereNotEqualTo("connections/likedBy", true)
}

Would have been working, only if your schema would have been structured this way:

Firestore-root
  |
  --- users (collection)
       |
       --- $uid (document)
            |
            --- connections (collection)
                  |
                  --- likedBy (document)
                       |
                       --- connections (object/map)
                             |
                             --- likedBy: true

But this is actually not the case.

answered Jun 18 '21 at 07:56

Alex Mamo

130,605
17
163
193

So lets say I execute the first query and in the onCompleteListener, I write the second query. How do I get the intersection as a result? – who-aditya-nawandar Jun 18 '21 at 09:27
What do you mean by "intersection as a result"? – Alex Mamo Jun 18 '21 at 09:27
So the first query gives me a list of users of a particular sex. The second one separately gives me a list of users with the condition (uid = true). But the second query won't have the clause for filter on the sex. I need users of a particular sex where likedBy (uid = true). I hope I am making sense to you. – who-aditya-nawandar Jun 18 '21 at 09:30
When you perform the first query, you already get only the users of a particular sex. Then perform the second query based on those users, **not** all users. You should also take into consideration storing all the data on which you want to do the filtering in the document itself. That way you can simply create a single collection. – Alex Mamo Jun 18 '21 at 09:40
I understand that the next query needs to be performed on the result of the first one. My question is, how? Also, I am not sure what exactly storing all the data in the document means. – who-aditya-nawandar Jun 18 '21 at 09:45
I guess apply is the keyword here. You have written that in the answer. – who-aditya-nawandar Jun 18 '21 at 09:48
Yes, adding as fields in the document all data on which you want to filter. You got it. – Alex Mamo Jun 18 '21 at 10:50
Unfortunately, can't change the schema. Trying to figure out how to run those 2 queries sequentially. I know your answer shows how to do it but I guess I am not smart enough to be able to implement it correctly. – who-aditya-nawandar Jun 18 '21 at 12:02
BTW, did you see the Update in the question? Am I doing it right? – who-aditya-nawandar Jun 18 '21 at 12:03
Yes, nesting a database call beneath another database call, is the way to go ahead. – Alex Mamo Jun 18 '21 at 12:26
One more thing, never ignore errors. Besides that, you might also consider trying using Kotlin Coroutine. – Alex Mamo Jun 18 '21 at 12:27
I did think of using Coroutines but I am new to all this, I thought I'll get it done in plain code as I could get more references. Once I am able to achieve the desired result, I'll try doing it with Coroutines. – who-aditya-nawandar Jun 18 '21 at 12:30
Give it a try again with the nested listeners and adding all the data you want to filter in a single document, and keep me posted. – Alex Mamo Jun 18 '21 at 12:49
I haven't been able to get it to run successfully. I have updated what I have currently. It doesn't throw any errors but it doesn't output anything. – who-aditya-nawandar Jun 18 '21 at 14:36
When I run the query only for filter on the sex, it gives me the result. Please know that I am trying to query multiple collections. So how I have "likedBy", there's also "dislikedBy" under "connections". And I am not trying to fetch these for a single user but for all the users. – who-aditya-nawandar Jun 18 '21 at 14:40
Most likely it's because you didn't create an [index](https://stackoverflow.com/questions/50305328/firestore-whereequalto-orderby-and-limit1-not-working), right? – Alex Mamo Jun 21 '21 at 07:39
I did, but I am pretty sure its not right. And I don't know how to correct it. I have uploaded the image of the index – who-aditya-nawandar Jun 21 '21 at 07:48
In that case, please post as a new question using its own [MCVE](https://stackoverflow.com/help/mcve) everything you have tried, so I and other Firebase developers can help you. – Alex Mamo Jun 21 '21 at 07:48
Did you see the index image? – who-aditya-nawandar Jun 21 '21 at 07:49
I see it's a collection group query. It should indeed also work with that. It doesn't matter if it's a simple or a collection group query. – Alex Mamo Jun 21 '21 at 07:51
That's why I asked to create a fresh new question for that. – Alex Mamo Jun 21 '21 at 07:51
It did help me, let me just figure this out, I am close, I guess... I'll mark it as an answer. – who-aditya-nawandar Jun 21 '21 at 08:04
Ok, keep me posted. – Alex Mamo Jun 21 '21 at 08:12

score 0 · Answer 2 · answered Jun 17 '21 at 18:03

0

Instead of using "/", try using the dot "." Like so... whereNotEqualTo("connections.likedBy", true)

answered Jun 17 '21 at 18:03

Yonatan Dawit

589
7
11

That sets the path right still doesn't solve the problem of multiple where clauses. – who-aditya-nawandar Jun 17 '21 at 18:32
Look that up here https://stackoverflow.com/a/48037033/12028348 – Yonatan Dawit Jun 18 '21 at 00:35
Ya I already saw that, I can't figure out that would work in my case. – who-aditya-nawandar Jun 18 '21 at 05:14

How to apply multiple where clauses with different depth levels in data in firebase firestore?

2 Answers2