Firebase: Query to exclude data based on a condition

Question

I'm able to get the data for a particular value in the child using orderByChild and equalTo (cool that it works for nested child as well)

private void getData() {
        try {
            final DatabaseReference database = FirebaseDatabase.getInstance().getReference();

            database.child(Constants.TABLE_TASKS).orderByChild("user/id")
                    .equalTo("somevalue")
                    .addValueEventListener(new ValueEventListener() {
                        @Override
                        public void onDataChange(DataSnapshot dataSnapshot) {
                            Timber.d(dataSnapshot.toString());
                        }

                        @Override
                        public void onCancelled(DatabaseError databaseError) {
                        }
                    });
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

Is there an easy way to get the data where a particular value is not found, basically something like a notEqualTo("somevalue") ?

Cleaning up comments. I'm afraid I have more bad news though... — Frank van Puffelen, Aug 28 '16 at 20:26

score 22 · Answer 1 · edited Jan 31 '20 at 15:15

22

In the Firebase Query model you can not filter for inequality to a value.

But you can test for the absence of any value (essentially: the absence of a property). For example with this data model:

{
    child1: {
      "user": {
        "id": 1,
        "name": "puf"
      }
    },
    child2: {
      "user": {
        "id": 2,
        "name": "abe"
      }
    },
    child3: {
      "user": {
        "id": 3
      }
    }
}

I can query for children without a user/name property with:

ref.orderByChild('user/name').equalTo(null)

Which leads to the only child that doesn't have a name:

child3

Feel free to play with my jsbin to see if you get further: http://jsbin.com/liyibo/edit?js,console

Update: I knew I'd answered this before, but couldn't find it earlier. Here's the dupe: is it possible query data that are not equal to the specified condition?. It looks like I have a mistake in there, since clearly I'm testing for the absence of a property in the above code.

edited Jan 31 '20 at 15:15

AKd

501
4
17

answered Aug 28 '16 at 20:35

Frank van Puffelen

565,676
79
828
807

Thanks, Frank! But generally in case of user, I would want to filter if a child* was created by a certain user or not..in such cases id/name would never be null – Monu Surana Aug 28 '16 at 20:40
2

I know. Hence the first line in bold: there is no way to filter on nodes having a value different than your "marker value". Sorry I don't have better news. – Frank van Puffelen Aug 28 '16 at 20:46
1

Thanks, Frank! Thanks for the prompt answer. So I guess the only way would be to get all the values on the client and filter it there (a bit expensive operation though). I hope such a feature can be implemented in newer releases :) – Monu Surana Aug 28 '16 at 20:50
2

@FrankvanPuffelen hi frank, got a question, when you stated `ref.orderByChild('child/name').equalTo(null)`, do you mean that child1's parent node is named `child`? – Moses Aprico Oct 25 '16 at 15:09
Thank you. It gave more info. – Reddy Raaz Sep 04 '17 at 17:19

score 15 · Accepted Answer · answered Aug 28 '16 at 21:45

I think I've found the solution and this is more of how the database should be designed and actually now I understood the intention behind Firebase guideline

https://firebase.google.com/docs/database/android/structure-data

Original Design:

{
    child1: {
      "user": {
        "id": "id1",
        "name": "puf"
      }
    },
    child2: {
      "user": {
        "id": "id2",
        "name": "abe"
      }
    },
    child3: {
      "user": {
        "id": "id1"
        "name": "puf"
      }
    }
}

Updated Design:

So apart from the storing the id and name of the user, we should also store a node with the id itself as the key and mark it to true

{
    child1: {
      "user": {
        "id": "id1",
        "name": "puf"
        "id1": true
      }
    },
    child2: {
      "user": {
        "id": "id2",
        "name": "abe"
        "id2": true
      }
    },
    child3: {
      "user": {
        "id": "id1"
        "name": "puf"
        "id1": true
      }
    }
}

With the updated design, if i execute ref.orderByChild('user/id1').equalTo(true)

I would get output as Child1 and Child 3

and if i execute ref.orderByChild('user/id1').equalTo(null),

I would get Child2 as the output

Interesting approach, a bit more expensive in data stored, but certainly useful — DavidTaubmann, Mar 01 '17 at 01:22
I am using Firestore and I tried this technique by doing: ```ref => ref.where(`user.${userID}`, '==', null)``` in my query but it doesn't work... I actually have to set a property, equal to the userID, to null in my data in order to get the result in my query. The search continue to exclude a data in a query for Firestore ... — Quentin Fonck, Aug 17 '18 at 10:17
The question/answers are for Firebase Realtime Database, not Firestore. — Slbox, Sep 07 '21 at 01:18

Firebase: Query to exclude data based on a condition

2 Answers2

Linked

Related