DAVID EAST: We're good? OK. OK, now we're not good. OK, I think we're good. [MUSIC PLAYING] Hey there, everyone. Welcome to the fourth episode
of the Firebase Database for SQL Developer series. In this lesson, I'm going
to write eight SQL queries and then convert them one
by one to Firebase queries. So let's dive right
into the laptop. So right here, I have a
file called query.SQL. And at the top, I have an
example table of users. And at the top, we
show the columns. So we have UID, which
is the primary key. And then we have name,
email, age, and location. And then below, we
have the sample rows of a user named David
and a user named Alice. And below this is the
list of the eight queries that I'm going to write. And then next to this file,
I have another one called Query.ts. And TS stands for TypeScript,
which in this case, is essentially just
JavaScript for us. And I'm only using
TypeScript because it's going to give us some awesome
code complete in the browser. So right here, I have an
example data structure for the Firebase database. So we have a parent
key of users. And then below
the parent key, we have individual child
keys for each user. And this is the same list that
we have on the previous page. So let's go back, and let's
start writing some queries. So the first one is
select a user by UID. And this is probably one
of the most basic things you can do is select a
record by its primary key. So to do that, we'll
say, select star from users where
UID is equal to 1. And this will pull
back the row for David. And so if we wanted to do
this in the Firebase SDK, well, we see that we
have our users key, and then we have
our child key of 1. So to do this, we would create
a route reference first. And I'm going to do this up top
so every single query can also use this root
reference variable. So const root ref is
Firebase.database.ref. And so this root ref points
to every single piece of data in the database. And then using
this reference, we can then nest down to
any child references. So I can say, const 1 ref--
and I'm calling it 1 ref since it's the first
query that we're writing-- and I'm going to say that it's
root ref dot child of users. And so users is our parent key. And at this point, if I want
to go from the parent key to the child key of 1, I can
just call dot child again. And we are at the first key. And this is the same thing as
saying, select star from users where UID is equal to 1. And if we wanted to
retrieve this user, we could just do that by
attaching an on-value listener or something like that. But right now, I'm just going
to keep it to the reference. So the second query is find
a user by an email address. And this is almost exactly
like the query from above. We can say, select star
from users where email is equal to Alice@email.com. And this will select
our row for Alice. So how would we do this
in our Firebase database? Well, we'll create
a reference again. And we first need to get
to the reference for users, so root ref dot child of users. And we can't call dot
child again and nest down, because we can only nest down
to the keys that we know about. So what we can do,
though, is use one of the ordering functions. So we can say, order by
child, and then specify email. And at this point, we
can say-- equal to is our querying function-- and
say, equal to Alice@email.com. So we're getting to
the user section. We're ordering by
email, and then we're looking for that email
that is Alice@email.com. So back to our SQL
file, our third query is limit to 10 users. And this one is
also pretty easy. We say, select star
from users limit 10. In the Firebase
database land, we'll create a reference to
our parent reference. So const 3 ref is root
ref dot child of users. And then we will
say, order by key, because this will order
by all of the child keys. And then say, limit to first 10. And actually, the
Realtime database SDK is really smart for us. So we can actually get
rid of this order by key, and it will infer it for us. So the next query is get
all users whose name starts with the letter D.
So in SQL, I can do this using a like clause. And so I could say,
select all from users where name is like capital
D and then a wildcard. So now using the
Firebase database SDK, I'll create a reference
to our users reference, yet again, so root
ref of child of users. And now we want to order by
child and specify the name. So we're querying
based upon the name. And at this point, I can use
the start at querying function and say, start at
the capital letter D. And this will retrieve everyone
in the database whose name starts with the
capital letter D. Now sometimes there are problems
with dealing with Unicode. And so you want to
make sure you're getting back everything
within the Unicode range. And so to do that,
you can actually say, end at, and then, say
capital D slash UF8FF. And UF8FF is at one of the
highest points in the Unicode range. And so by doing the
start at/end at range, you make sure that you're
grabbing everything that starts with the letter D. So now the next SQL query is get
all users who are less than 50. So we'll say, select
star from users where age is less than 50. Now back in our
Firebase database file, we'll create a
reference to our users. So create another reference,
root ref dot child of users. And now I want to order by the
age, so order by child of age. And I can say, dot
end at, and put 49. And the reason why
I'm saying, end at, is because we're
essentially saying, order by all the
children, but let's not go any higher
than the children whose ages are greater than 49. So we won't hit anyone who's
50, so that is less than 50. So the next query is get all the
users who are greater than 50. So this is pretty
much the same query. We can just go and
copy and paste it. So select star from users
where age is greater than 50. So back in the other file,
we'll create another reference, because remember, we always
have to create a reference to the parent key. In this case, that is users. We'll order by child of age. And now I will say, start at 51. So this is similar to the
previous query we did. In this case, we're saying,
order by child of age, but let's only start
retrieving the children once their age is 51, because
51 is greater than 50. Now the next one is
get all the users who are between 20 and 100. So to do this range query,
I'll say, select star from users where age is
greater or equal to 20 and age is less than
or equal to 100. So this is similar to using
the start at or end at, but we're actually going
to use them together. So we'll create our reference. So root ref dot child
of users, order by age, and we want to start at 20. And we want to make
sure we're actually starting at 20, because
our range is between 20-- so equal to 20 or greater-- and
then less than or equal to 100, so dot end at 100. And last query, number eight,
is get all users who are 28 and live in Berlin. So to do that, we'll
say, select star from users where age is
equal to 28, and location is equal to Berlin. So we'll jump over to the
Firebase database file. And we'll say, const eight ref
is root ref dot child of users. And so you might
be thinking, OK, well, the first part of this
clause is users who are 28. So I'll say, dot order by child
of age and say equal to 28. Then the second part of this is
users who also live in Berlin. So I'll say order
by child of location and say equal to Berlin. But this query right here
will actually throw an error. And the reason why is because
you can only use one ordering function in the Firebase SDK. So how do we get users who
are 28 and live in Berlin? So whenever you structure
data in the Firebase database, you want to do so with
your queries in mind so you can optimize your data
structure for the questions that you're going to
ask it down the line. So taking a look at this data
structure, we have users, and then the child
key is their UID. So the quickest fix we
can actually do right now is combine the age and
location keys together and create our own index. So to do that, we can say
age_location and set that to 99_SF, because 99 is the
age and the location is SF. And then in the second user,
we can do the same thing again-- age_location. And the age is 28, and
the location is Berlin. And then now down here,
we can just move back to using one order by method. And we could say, order
by child of age_location equal to 28 Berlin. And just like that, we
are getting users who are 28 and who live in Berlin. So that's all for this lesson. If you found it helpful, make
sure to Like and subscribe. And you notice in
this lesson, we wrote eight
different SQL queries and then converted them
all to Firebase queries. But we only queried one
table in the SQL database and just one collection
in the Firebase database. And coming from a
SQL database, you're probably used to
writing joins where you can join multiple tables
to get one result set. So how would we handle
joins in Firebase database? Well, stay tuned to
the next lesson where we'll cover that in depth. So that's all. And I will see you
in the next lesson. Thanks for watching our video. You might also want to check out
this video, or even this one. I really like that one. And make sure to subscribe. And now you can
just watch me try to catch popcorn in my mouth. Oh, two in a row.
