Common SQL Queries converted for the Firebase Database - The Firebase Database For SQL Developers #4

By: Firebase

773   72   44562

Uploaded on 12/09/2016

Check out our old, but still good blog post: https://goo.gl/YzkWsC

Welcome to the fourth video in the Firebase Database for SQL Developers series!

This video translates 8 common SQL queries to Firebase Database queries.

Watch more videos from this series: https://goo.gl/ZDcO0a

Subscribe to the Firebase Channel: https://goo.gl/9giPHG

Comments (15):

By hackerboos    2017-09-20

Until this[1] is fixed I'm not sure that Firebase would meet my needs for many projects. It's such a fundamental requirement to be able to query multiple fields without creating a mess of permutations of each field.

[1] - https://youtu.be/sKFLI5FOOHs?t=541

Original Thread

By anonymous    2017-09-20

queryOrderedByChild can be used only once. a workaround would be

   |_objectiId1
   |  |_ userId:1
   |  |_ timestamp:143242344
   |  |_ userId_timestamp:1_143242344
   |_objectiId2
      |_ userId:1
      |_ timestamp:143243222
      |_ userId_timestamp:1_143243222

Then try :

FIRDatabaseQuery *query = [[firebase queryOrderedByChild:@"userId_timestamp"] queryEqualToValue:@"1_143242344"];

check this out https://youtu.be/sKFLI5FOOHs?t=541


another way to do it would be :

 |_objectiId1
       |_ userId1:
       |      |_ objectId11:143242344
       |      |_ objectId12:143243222
       |_ userId2:    

Then querying path is http://firbasedbname.com/Recent/UniversityId/userId1

and then order by value

Original Thread

By anonymous    2017-09-20

For your first question.

Before you use the EqualTo filter there should first be an OrderBy filter... This OrderBy filter tells the query which field should EqualTo refer to... So your query should look like this.

let path = this.API_EVENTS_PATH;
return db.list(path , {
  query: {
    orderByChild: 'from',
    equalTo: 'sharikov.vladislav@gmail.com',
  }
});

For your second question

How to filter items by 2 fields

In Nosql database like firebase you are normally allowed to filter by only 1 field. Every other manipulations are done considering on how you organise your database. Check out this firebase video which shows you how to do your normal SQL queries on firebase database. Enjoy

Original Thread

By anonymous    2017-09-20

Current Firebase Database API does not provide exact way for combining queries.
However, in the video person from Firebase tells how to structure your database to relieve the burden.
I suggest, instead of seperating leftEyeHeight and leftEyeWidth you can merge them up by simply leftEyeSize whose value could be written as (leftEyeHeight, leftEyeWidth) This will halve your problem even if you write each query manualy

Original Thread

By anonymous    2017-10-01

Your database structure is good but unfortunately, Firebase queries can only order/filter by a single property. So there is no WHERE clause within Firebase. What should you do instead, is to couple a compound value named name_username. Your database structure should look like this:

{
   users: {
      user_id_of_who_this_username_belongs_to: {
         name: "name1",
         username: "test1",
         name_username: "name1_test1"
      },
      user_id_of_who_this_username_belongs_to: {
         name: "name2",
         username: "test2",
         name_username: "name2_test2"
      },

   }
}

As you probably see, i have added the new compound value name_username for each user. This means that you can query your Firebase database according to this new name_username field. Note, that i have removed bio node, because there is no need for an extra child in your structure. Assuming that users node is a direct child of your Firebase root, please use the following code:

DatabaseReference rootRef = FirebaseDatabase.getInstance().getReference();
DatabaseReference usersRef = rootRef.child("users");
ValueEventListener eventListener = new ValueEventListener() {
    @Override
    public void onDataChange(DataSnapshot dataSnapshot) {
        String searchedNameOrUserName = "test2";
        for(DataSnapshot ds : dataSnapshot.getChildren()) {
            String name_username = ds.child("name_username").getValue(String.class);
            if(name_username.contains(searchedNameOrUserName)) {
                Log.d("TAG", name_username);
            }
        }
    }

    @Override
    public void onCancelled(DatabaseError databaseError) {}
};
usersRef.addListenerForSingleValueEvent(eventListener);

I gave you an example for searching test2 keyword. The searchNameOfUserName text would be the exact searched name or username typed by the user.

To better understanding, i suggest you see this tutorial.

You can also use Denormalization which is normal with the a Firebase Database. Accordingly, you can create another node named namesUserNames in which you can add only the desired information after you can search. Your new node should look like this:

{
   namesUserNames: {
      user_id_of_who_this_username_belongs_to: {
          name_username: "name1_test1"
      },
      user_id_of_who_this_username_belongs_to: {
          name_username: "name2_test2"
      },
   }
}

Using this example, you don't need to download the entire users node, you'll be able to download only this particular one. Which saves bandwith.

Original Thread

By anonymous    2017-10-01

Firebase queries can only order/filter by a single property. So there is no WHERE clause within Firebase. What should you do instead, is to couple a compound value named location_mode_spinnerOne. Your database structure should look like this:

Firebase-root
   |
   -- Students Reports
         |
         -- Jasna Kuljancic
               |
               -- Clinical First
                     |
                     -- -KuVRQ4OjdfKXCNdLWzb
                            |
                            --- data: 3939393
                            |
                            --- location: "fifififi"
                            |
                            --- mode: "ododododo"
                            |
                            --- spinnerOne: "Asylum Hill Family Clinic"
                            |
                            --- location_mode_spinnerOne: "fifififi_ododododo_Asylum Hill Family Clinic"

As you probably see, i have added the new compound value location_mode_spinnerOne for each particular category. This means that you can query your Firebase database according to this new location_mode_spinnerOne field. Assuming that the above database structure is correct, please use the following code:

DatabaseReference rootRef = FirebaseDatabase.getInstance().getReference();
DatabaseReference categoryRef = rootRef.child("Students Reports").child(fullname).child(CATAGORY);
ValueEventListener eventListener = new ValueEventListener() {
    @Override
    public void onDataChange(DataSnapshot dataSnapshot) {
        String searchedText = "fifififi";

        for(DataSnapshot ds : dataSnapshot.getChildren()) {
            String location_mode_spinnerOne = ds.child("location_mode_spinnerOne").getValue(String.class);
            if(!location_mode_spinnerOne.contains(searchedText)) {
                categoryRef.child(uniqueID).setValue(subreport);
            }
        }
    }

    @Override
    public void onCancelled(DatabaseError databaseError) {}
};
categoryRef.addListenerForSingleValueEvent(eventListener);

I gave you an example for searching fifififi keyword. The searchedText text would be the exact searched text typed by the user.

To better understanding, i suggest you see this tutorial.

Original Thread

By anonymous    2017-10-22

My suggestion was either change the structure on the database or use this query which seems to have solved the problem:

.orderByKey().startAt("rock")

I'm not so sure it will work for all your future cases tho because I think the query will start at rock but not end at rocky if you have more keyboards after rocky. I am just basing this on this source: Common SQL Queries converted for the Firebase Database

enter image description here

Original Thread

By anonymous    2018-01-01

I found solution from Common SQL Queries converted for the Firebase Database.

var currentUserUID = firebase.auth().currentUser.uid;
let dbRef = firebase.database().ref("ARUserProfiles/" + currentUserUID);
dbRef
  .orderByChild("email")
  .equalTo("tomcruise@firebasemyapp.com")
  .once(
    "value",
    response => {
      const val = response.val();
      let responsePayload;
      if (val) {
        responsePayload = Object.entries(val);
        console.log("ARUserProfiles :-", responsePayload);
      }
    },
    error => {
      console.log(error);
    }
  );

Original Thread

By anonymous    2018-01-29

If you're new to NoSQL, I recommend reading [NoSQL data modeling](https://highlyscalable.wordpress.com/2012/03/01/nosql-data-modeling-techniques/) and watching [Firebase for SQL developers](https://www.youtube.com/playlist?list=PLl-K7zZEsYLlP-k-RKFa7RyNPa9_wCH2s), specifically the [video on common queries](https://www.youtube.com/watch?v=sKFLI5FOOHs&t=1s&list=PLl-K7zZEsYLlP-k-RKFa7RyNPa9_wCH2s&index=4).

Original Thread

By anonymous    2018-03-12

Oficial firebase video: https://www.youtube.com/watch?v=sKFLI5FOOHs&t=401s Min: 10:30. The last query have 2 values (age and location).

Original Thread

By anonymous    2018-05-01

Suppose your structure is like this:

enter image description here

You can do something like this, this will grab all nodes from messages that start with id1:

String id1 = "id1";
        DatabaseReference ref = FirebaseDatabase.getInstance().getReference("messages");
        ref.orderByKey().startAt(id1).endAt(id1 + "\uf8ff").addListenerForSingleValueEvent(new ValueEventListener() {
            @Override
            public void onDataChange(DataSnapshot dataSnapshot) {
                for (DataSnapshot snap : dataSnapshot.getChildren()) {
                    Log.d("SNAP", snap.getValue(String.class));
                }
            }

            @Override
            public void onCancelled(DatabaseError databaseError) {

            }
        });

I took the idea from the javascript implementation, but it also worked in Android, I've run a test on my device/database. Note that this method will work to grab data that starts with the given id.

EDIT:

The idea of querying firebase data that contains a certain string is discussed in this official post. Also, this question as well. The bottom line is that the api doesn't support these types of queries, the approach I mentioned above is the closest you can get of implementing a "SQL LIKE" in firebase. Currently, there's no way of searching for strings that END with another string. The endAt doesn't mean the string ends with id1, but rather that the range of values I want to retrieve finishes at (id1 + "\uf8ff"), that means any string starting with id1. Your options are either change the schema or grab all messages and search locally (the suggestions of the other two answers).

Original Thread

By anonymous    2018-05-29

This is my database:

enter image description here

And I want to delete the "small_green" value, so I'm trying with this:

const refToDelete = firebase.database().ref().child('products').orderByChild('fruits').equalTo('small_green');
refToDelete.remove();

But it throws this error:

enter image description here

I'm following this tutorial and this documentation.

What I'm doing wrong?

Original Thread

Popular Videos 10757

Submit Your Video

If you have some great dev videos to share, please fill out this form.