00:00
[music]
00:05
So now that we have both our start seeder and fake seeder defined
00:07
and we've executed them, we actually have data inside of our database
00:10
that we can work with now.
00:11
So let's go ahead and boot up our server here,
00:13
jump into our terminal and run npm run dev.
00:17
Let's jump into our browser and take a look at that.
00:19
We have three movies showing up here.
00:20
Love Hangover, which I'm not sure if you ever could,
00:23
Streets of Philadelphia and Smells Like Teen Spirit.
00:25
Remember within Faker, we're using song lyrics
00:27
for the titles for our movies here.
00:29
And then just some warm text here for the summary.
00:32
If we click into one of these, we see the actual movie's details.
00:35
So here's the abstract, the title.
00:37
And then within our URL, we're currently using a UUID
00:39
in place of our human legible slug with our factory.
00:43
So everything appears to be working A-OK.
00:45
I also got rid of our two links right up here
00:47
as they're no longer applicable because we're no longer going to be working
00:50
with our locally hosted movies.
00:52
OK, so let's hide our browser back away
00:53
and let's get back to our text editor here.
00:55
And let's jump back into our movies controller.
00:57
So right here is where all of that magic is happening.
00:59
Whenever we're viewing our home page,
01:01
we're getting all of our movies back via the movie.all method,
01:04
which will query all of our movies.
01:06
And whenever we click into the movie show page,
01:07
that's where we're running our find by or find by or fail method
01:10
to get back the movie detail results.
01:13
Remember that find by and find by or fail will run the same thing.
01:16
The only difference is find by will not error out,
01:19
but instead return back null if it cannot find a result,
01:22
as noted by the type right up here.
01:24
Whereas find by or fail will fail with a 404
01:26
if it cannot find a result, meaning that the movie is not nullable
01:29
as an error will occur if it cannot find it.
01:32
Now, the static query methods that we've been using so far
01:33
are fantastic for simplistic use cases to getting to your data.
01:37
But there's going to be more complex scenarios
01:39
where you need to maybe even filter down your data using dynamic filters
01:42
to get to the underlying data that you want to display to your users.
01:46
And that's where the model query builder
01:48
or just a query builder in general comes into play.
01:51
So if we dive into our browser here,
01:53
let's open up the Lucid documentation.
01:55
So that's lucid.adonisjs.com.
01:57
And there is a query builder section right here.
01:59
There's one for selecting data, inserting data,
02:01
and then raw queries as well.
02:02
We'll be focusing on the select query builder here today.
02:05
And what they're showing us here is the database modules query builder.
02:08
We haven't talked about the database module quite yet,
02:10
but essentially it's raw access going outside the realms of our models
02:14
to your database.
02:16
For example, if we do db.from users,
02:19
we would be running a query against our users table.
02:22
And the results that we would get back would be the column names
02:25
and all of its data as it's defined inside of our database,
02:28
rather than how it's defined inside of our model.
02:30
For example, we can open up a REPL session.
02:32
So let's stop our server, node, ace, REPL.
02:36
We'll do .ls to see our available commands.
02:38
Okay, so we see we have load models as well as load db.
02:41
This db is the database module that we're taking a look at right here.
02:45
So if we go ahead and await load db,
02:49
and while we're here, we'll also await load models.
02:52
So let's compare the two results just using our movies.
02:54
So let's do await db,
02:57
as we can see we have available right here by loading our db,
02:59
.from to query from our movies.
03:02
And I will do .first just so that we get back a single result here.
03:06
We'll hit enter, and you see that we get back our movies
03:09
as it's defined inside of our database
03:10
using snake case for our columns as our database structure is doing.
03:15
However, if we do await models.movie,
03:18
we can do .query to access the models query builder,
03:21
and we'll just run first there as well.
03:23
And just as we were working with before,
03:25
you'll see that we get back an instance of our model.
03:27
The underlying attributes that we have at our disposal
03:30
are as defined inside of our model.
03:32
So they're using camel case rather than snake case as our database is using.
03:36
And we also have all of the fun attributes that Lucid uses
03:39
to keep track of changes, transactions,
03:41
and all of that fun stuff with our model as well.
03:43
So whenever we mentioned the db module,
03:45
that's kind of raw access to our database
03:47
going outside the realms of our models.
03:50
And whenever we mentioned the model query builder or models in general,
03:52
that's going to work with model instances and the like.
03:56
Now, while we're here, there is a method on the model query builder
03:59
that will allow us to get to that raw database information,
04:02
and that's called Poyo.
04:03
So if we scroll down, hit up to go to our last REPL command
04:06
and add in .pojo to the end of our query builder,
04:10
hit enter on that.
04:11
Oh, whoops, we need to run that before our first.
04:13
So let's go back over to here, .poyo, hit enter there.
04:17
There we go. You'll see that although now we're using
04:19
our model query builder, this Poyo command is giving us raw access
04:22
to the results as it would be defined inside of our database.
04:25
So we're all back to Snake Case.
04:27
Cool. So now we know the difference between the two query builders,
04:29
and we know how we can get back to the raw database query results
04:32
using the model query builder.
04:34
Let's continue onward with taking a look at the query builder as a whole.
04:36
So here on the right hand side, we see all of the different methods
04:39
that we're going to have at our disposal using the query builder as a whole.
04:41
Primarily, the only difference and exclusion here is the from method
04:45
whenever we're working with the model query builder,
04:47
because it's working specifically with a model.
04:49
So it already knows where we want to run the query from.
04:52
Remember, the from method on the database query builder
04:54
is just specifying the table that we want to run the query against.
04:57
That's primarily going to be the main difference between the two
04:59
whenever you're looking at this select query builder page.
05:02
Everything else we should have at our disposal
05:04
on both the query builders here.
05:06
And you're going to see it's a lot of different methods
05:09
that we can use to actually build out queries.
05:11
It's just going to keep going on and on and on.
05:13
And the query builder that AdonisJS uses
05:15
is actually built on top of Connects.js.
05:17
So if we head over to the Connects.js documentation,
05:20
go into the view guide,
05:21
we can scroll down to the query builder section here.
05:24
And this documentation here should for the most part be applicable
05:28
to the AdonisJS query builder as well,
05:29
since it's built on top of this query builder.
05:32
So you do have these two different documentations at your disposal
05:35
should you need them.
05:36
For the most part, I would reach for the Lucid one first,
05:38
since it will have some variations from the Connects.js documentation
05:42
in terms of its application.
05:43
So I'm going to go ahead and hide that back away.
05:45
And there's a lot of different methods that we could do.
05:47
Most of these are going to be outside the realm
05:49
of your typical use case,
05:51
but some of them you will need for almost every single query.
05:54
And that's where statements, order bys, limitations.
05:57
Before we jump into that,
05:59
let's scroll down a little bit more to the model section
06:01
inside the documentation.
06:02
There's a query builder section here as well.
06:04
This specifies methods that the model query builder has
06:08
that the database query builder does not.
06:10
For the most part, these are going to be relationship based.
06:12
So preload with count with aggregates has,
06:14
where has, side loaded.
06:15
Those are all going to be relationship based.
06:18
With scopes and apply are specific to scopes,
06:21
which are outside the realm of this particular lesson.
06:23
And we've already talked about Poyo.
06:24
And then we have paginate,
06:25
which will allow us to get back paginated results.
06:28
And we can access a reference to the model as well.
06:31
Okay, cool. So let's hide that back away.
06:33
And inside of a REPL session here,
06:34
I'm just going to go ahead and hide our text editor back away there too.
06:37
Let's learn how we can use where statements
06:38
to filter down on the data that we're querying.
06:40
So we can do await models, movie dot query
06:44
to access the model query builder.
06:46
And we previously ran the first.
06:47
So we just grabbed the first result out of the database.
06:50
Instead, let's do where to specify a where clause.
06:54
And we can specify a column name as the first argument here.
06:56
So we could do title.
06:58
And then we can do one of two things here.
06:59
We can either specify three arguments or two arguments as a whole.
07:03
If we specify three,
07:04
the middle argument is going to be the comparison logic.
07:07
By default, if we specify just two arguments,
07:10
that'll be an equals check.
07:11
So we'll do equals there.
07:12
And then we'll provide in the value as the third argument.
07:15
So we'll do smells like teen spirit.
07:17
We can hit enter here to run that query.
07:19
And we're going to get back a proxied result of our model instance,
07:23
which is going to wrap up our attributes.
07:24
So we can actually see the underlying results that we got back.
07:27
For that reason, we'll end this with our Poyo,
07:30
which will return back the data as we're seeing inside of the database.
07:33
So I enter there and there we go. Okay, cool.
07:35
So we do indeed get back our smells like teen spirit result.
07:38
And if we remove that third argument,
07:41
we'll see that we get back the exact same thing
07:42
because it will default to just doing an equals check.
07:45
There we go. But maybe we want to do a likeness check instead.
07:48
So we could provide that in place as our middle argument there.
07:52
And then maybe we could do where the title starts with smells.
07:55
So we'll take this back, hit percent there to say anything can be after smells.
08:00
We'll run this and we get back the exact same result.
08:02
We could try switching this to something different.
08:04
So maybe love as I think we had love hangover as a result.
08:07
Yep, there we go.
08:08
And we get back love hangover as a result there because it starts with love.
08:12
We could do something similar.
08:13
So we could do where created at is greater than
08:17
or you could do equals to if you wanted to 2024, 02.
08:21
And maybe we'll do 24, which is a day before these results were created.
08:25
We hit enter there and we get back all three of our results
08:28
because we created all three movies on the same day.
08:30
If however, we switch this to 26 a day after they were created,
08:35
we'll get back no results.
08:37
But there's different variants to the where statement here as well.
08:39
So if we go back up a couple of steps to where we were doing our where like,
08:42
instead of providing three arguments to get to the like,
08:45
we could provide two and instead use the where like method,
08:48
where now the default comparison logic will be a like statement.
08:51
So if we run this, we'll get back our result as expected.
08:54
And there is also a where I like to do a case insensitive likeness check.
08:59
So for example, if we switch our love to a lowercase L,
09:03
hit enter there, we still get back a result.
09:04
But if we get rid of that I and the where I like,
09:06
we won't get back a result because there's no capitalization matches
09:10
for the love check here.
09:11
If we dive back into the loose documentation,
09:14
scroll back up to the select query builder statements,
09:17
there is a bunch of different variations of the where statement.
09:19
So you have where I like, where like, where, where column,
09:23
where in, where null, where exists, where between, where raw, where JSON.
09:27
And on top of each and every single one of those,
09:30
there is also an and and an or variation.
09:33
So for example, if we wanted to do where like title,
09:36
we'll switch this back to a capital L so that we actually have a match.
09:39
Starts with love and the status ID is one.
09:43
We could add in an and where,
09:45
specify the column as a status ID and the value as one, run that.
09:50
And now we're going to get back results where the title starts with love
09:53
and where the status ID is one.
09:55
Now, the default behavior,
09:56
whenever you chain multiple where statements together
09:59
is going to be to do an and check.
10:02
So we could just run where here again,
10:04
and it will be the exact same thing as our results before,
10:07
but there's also an or variation as well.
10:10
So we could do or where status ID is one.
10:13
All of our movies currently have a status ID of one.
10:15
So because of this, we're going to get back all three movies
10:18
as our results here, because they all have a status ID of one.
10:22
And we ran where title starts with love or where the status ID is one.
10:26
If we want to just two results back, we could limit the results.
10:30
So let's go back to our previous query.
10:32
Dot limit to, to limit the results to just two.
10:35
So if we hit enter there, now we just have two results that came back.
10:38
The third one was omitted and we can order these as well.
10:42
So we can do order by, maybe created at,
10:45
we can either do ascending or ASC, which will be the default sort,
10:49
or we can do descending to go in the inverse.
10:52
So ascending is going to be an alphabetical order.
10:54
It's going to be A to Z, one to 100, the first to the 30th.
10:58
Descending would then be the inverse order.
11:00
So it would be Z to A, 100 to one and the 30th to the first.
11:04
So if we go ahead and run this in descending order,
11:07
they were all created in almost the exact same time.
11:09
We look at the very last character here,
11:11
we're going to see a very slight difference.
11:13
The first result that we got back was dot 761 Zulu time.
11:16
And the second result that we limited to was dot 760.
11:20
So very, very slight difference,
11:21
but it is indeed going in descending order.
11:24
If we switch this now to ascending, so ASC,
11:28
or that'll be default so we can get rid of that argument there altogether.
11:31
Hit enter. A little bit bigger of a difference here.
11:33
We can see we have 754 Zulu time as our first argument,
11:37
and then it's going upwards to 760 Zulu time.
11:40
So now we're going in ascending order based off of our created at.
11:42
So there's just way too much to be able to cover everything
11:45
with these query builders.
11:46
But there's a couple of things that I do want to touch on
11:49
because they're very useful.
11:51
So first, you can pass a callback argument to your where statement,
11:55
and that will essentially wrap that statement in parentheses
11:58
in terms of the underlying SQL that's generated.
12:00
For example, we can await models dot movie dot query
12:04
to get our query builder for a movie dot where.
12:07
Let's say status ID is one, which matches all of our results.
12:11
We can do an additional where statement to say,
12:13
and pass in a callback function to now do query dot where
12:17
within parentheses here.
12:19
And we can now do ID is one or where ID is two.
12:25
So if we run this, we didn't add Poyo on there.
12:27
So we're getting back. Let's do that real quick.
12:29
So Poyo so that we can actually see the objects.
12:31
Run that. Now we get back two results where the ID is one and two.
12:35
All three of our movies have a status ID of one.
12:38
So all three match this result.
12:40
But now we have a separate parenthesized statement
12:43
where the movie has an ID of one or two.
12:47
If we run that again, but change our first where statement slightly
12:50
to something that just one of these results matches.
12:53
So maybe where the title starts with streets.
12:55
So we can do where like switch our column to title and our value to streets
13:01
with a percent sign afterwards to say that anything can come after streets.
13:04
Run this. We'll just get back one result where the title starts with streets
13:09
and the ID is either one or two.
13:11
And we're getting back just the one result here because the callback function
13:14
that we're providing to the where statement here is wrapping our where
13:18
and our or where in parentheses.
13:20
So it needs to match one of these two and our where like,
13:24
which is different than if we just did wait models, movie query,
13:28
where like title starts with streets, where ID is one or where ID is two.
13:36
Actually, let's switch the or where there, make it a little bit more evident
13:39
where ID is two or one.
13:41
And we'll do OYO so that we can actually see the results.
13:43
Run that. And now you see that we get back both results
13:46
because this statement is different than this one.
13:49
This one's wrapping this where and or where for our ID check in parentheses.
13:54
So it needs to match either ID of one or two and a title that starts with streets.
13:59
Whereas this one has our second where statement outside of parentheses.
14:04
So it needs to have a title starting with streets and an ID of two or just an ID of one.
14:09
So you can see how the context kind of changes there depending on whether or not
14:12
we're providing our where statement inside of a callback,
14:15
which will wrap it in parentheses or just as a straight chain.
14:18
Additionally, we can do an if check to conditionally apply a where as well.
14:23
So let's do let title filter equal an empty string.
14:26
We'll define that. So now if we print out title filter, we'll get back an empty string.
14:31
Cool. So we can do a wait models, movie dot query to access the query builder.
14:36
And instead of just doing where title has the value of our title filter,
14:41
which will return back in the results because we don't have any titles that are an empty string,
14:46
we can instead provide this where statement inside of an if check.
14:49
So we do if title filter provide the second argument as a callback function query, just like so.
14:54
With this if check, if title filter is truthy,
14:57
it will run the first argument that we have that callback.
15:00
Otherwise, we can add in an else statement as well as a third argument
15:04
that's also a callback to do query dot where something else.
15:08
Don't really have anything to do an else for in this particular use case.
15:11
So we'll run this and you'll see that we get back all three of our results.
15:14
But if we switch title filter to equal love hangover, which is one of our movies,
15:19
and we try to run that query again, you'll see that we just get back love hangover.
15:23
We can't see it, but if we add Poyo to the end of that,
15:26
we'll be able to see that we do indeed just get back love hangover.
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!