Querying Our Movies with the Query Builder
In this lesson, we'll introduce both the database and model query builder. We'll learn the differences between the two and the basics of how to use them.
- Author
- Tom Gobich
- Published
- Mar 05
- Duration
- 15m 30s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
Querying Our Movies with the Query Builder
-
[music]
-
So now that we have both our start seeder and fake seeder defined
-
and we've executed them, we actually have data inside of our database
-
that we can work with now.
-
So let's go ahead and boot up our server here,
-
jump into our terminal and run npm run dev.
-
Let's jump into our browser and take a look at that.
-
We have three movies showing up here.
-
Love Hangover, which I'm not sure if you ever could,
-
Streets of Philadelphia and Smells Like Teen Spirit.
-
Remember within Faker, we're using song lyrics
-
for the titles for our movies here.
-
And then just some warm text here for the summary.
-
If we click into one of these, we see the actual movie's details.
-
So here's the abstract, the title.
-
And then within our URL, we're currently using a UUID
-
in place of our human legible slug with our factory.
-
So everything appears to be working A-OK.
-
I also got rid of our two links right up here
-
as they're no longer applicable because we're no longer going to be working
-
with our locally hosted movies.
-
OK, so let's hide our browser back away
-
and let's get back to our text editor here.
-
And let's jump back into our movies controller.
-
So right here is where all of that magic is happening.
-
Whenever we're viewing our home page,
-
we're getting all of our movies back via the movie.all method,
-
which will query all of our movies.
-
And whenever we click into the movie show page,
-
that's where we're running our find by or find by or fail method
-
to get back the movie detail results.
-
Remember that find by and find by or fail will run the same thing.
-
The only difference is find by will not error out,
-
but instead return back null if it cannot find a result,
-
as noted by the type right up here.
-
Whereas find by or fail will fail with a 404
-
if it cannot find a result, meaning that the movie is not nullable
-
as an error will occur if it cannot find it.
-
Now, the static query methods that we've been using so far
-
are fantastic for simplistic use cases to getting to your data.
-
But there's going to be more complex scenarios
-
where you need to maybe even filter down your data using dynamic filters
-
to get to the underlying data that you want to display to your users.
-
And that's where the model query builder
-
or just a query builder in general comes into play.
-
So if we dive into our browser here,
-
let's open up the Lucid documentation.
-
So that's lucid.adonisjs.com.
-
And there is a query builder section right here.
-
There's one for selecting data, inserting data,
-
and then raw queries as well.
-
We'll be focusing on the select query builder here today.
-
And what they're showing us here is the database modules query builder.
-
We haven't talked about the database module quite yet,
-
but essentially it's raw access going outside the realms of our models
-
to your database.
-
For example, if we do db.from users,
-
we would be running a query against our users table.
-
And the results that we would get back would be the column names
-
and all of its data as it's defined inside of our database,
-
rather than how it's defined inside of our model.
-
For example, we can open up a REPL session.
-
So let's stop our server, node, ace, REPL.
-
We'll do .ls to see our available commands.
-
Okay, so we see we have load models as well as load db.
-
This db is the database module that we're taking a look at right here.
-
So if we go ahead and await load db,
-
and while we're here, we'll also await load models.
-
So let's compare the two results just using our movies.
-
So let's do await db,
-
as we can see we have available right here by loading our db,
-
.from to query from our movies.
-
And I will do .first just so that we get back a single result here.
-
We'll hit enter, and you see that we get back our movies
-
as it's defined inside of our database
-
using snake case for our columns as our database structure is doing.
-
However, if we do await models.movie,
-
we can do .query to access the models query builder,
-
and we'll just run first there as well.
-
And just as we were working with before,
-
you'll see that we get back an instance of our model.
-
The underlying attributes that we have at our disposal
-
are as defined inside of our model.
-
So they're using camel case rather than snake case as our database is using.
-
And we also have all of the fun attributes that Lucid uses
-
to keep track of changes, transactions,
-
and all of that fun stuff with our model as well.
-
So whenever we mentioned the db module,
-
that's kind of raw access to our database
-
going outside the realms of our models.
-
And whenever we mentioned the model query builder or models in general,
-
that's going to work with model instances and the like.
-
Now, while we're here, there is a method on the model query builder
-
that will allow us to get to that raw database information,
-
and that's called Poyo.
-
So if we scroll down, hit up to go to our last REPL command
-
and add in .pojo to the end of our query builder,
-
hit enter on that.
-
Oh, whoops, we need to run that before our first.
-
So let's go back over to here, .poyo, hit enter there.
-
There we go. You'll see that although now we're using
-
our model query builder, this Poyo command is giving us raw access
-
to the results as it would be defined inside of our database.
-
So we're all back to Snake Case.
-
Cool. So now we know the difference between the two query builders,
-
and we know how we can get back to the raw database query results
-
using the model query builder.
-
Let's continue onward with taking a look at the query builder as a whole.
-
So here on the right hand side, we see all of the different methods
-
that we're going to have at our disposal using the query builder as a whole.
-
Primarily, the only difference and exclusion here is the from method
-
whenever we're working with the model query builder,
-
because it's working specifically with a model.
-
So it already knows where we want to run the query from.
-
Remember, the from method on the database query builder
-
is just specifying the table that we want to run the query against.
-
That's primarily going to be the main difference between the two
-
whenever you're looking at this select query builder page.
-
Everything else we should have at our disposal
-
on both the query builders here.
-
And you're going to see it's a lot of different methods
-
that we can use to actually build out queries.
-
It's just going to keep going on and on and on.
-
And the query builder that AdonisJS uses
-
is actually built on top of Connects.js.
-
So if we head over to the Connects.js documentation,
-
go into the view guide,
-
we can scroll down to the query builder section here.
-
And this documentation here should for the most part be applicable
-
to the AdonisJS query builder as well,
-
since it's built on top of this query builder.
-
So you do have these two different documentations at your disposal
-
should you need them.
-
For the most part, I would reach for the Lucid one first,
-
since it will have some variations from the Connects.js documentation
-
in terms of its application.
-
So I'm going to go ahead and hide that back away.
-
And there's a lot of different methods that we could do.
-
Most of these are going to be outside the realm
-
of your typical use case,
-
but some of them you will need for almost every single query.
-
And that's where statements, order bys, limitations.
-
Before we jump into that,
-
let's scroll down a little bit more to the model section
-
inside the documentation.
-
There's a query builder section here as well.
-
This specifies methods that the model query builder has
-
that the database query builder does not.
-
For the most part, these are going to be relationship based.
-
So preload with count with aggregates has,
-
where has, side loaded.
-
Those are all going to be relationship based.
-
With scopes and apply are specific to scopes,
-
which are outside the realm of this particular lesson.
-
And we've already talked about Poyo.
-
And then we have paginate,
-
which will allow us to get back paginated results.
-
And we can access a reference to the model as well.
-
Okay, cool. So let's hide that back away.
-
And inside of a REPL session here,
-
I'm just going to go ahead and hide our text editor back away there too.
-
Let's learn how we can use where statements
-
to filter down on the data that we're querying.
-
So we can do await models, movie dot query
-
to access the model query builder.
-
And we previously ran the first.
-
So we just grabbed the first result out of the database.
-
Instead, let's do where to specify a where clause.
-
And we can specify a column name as the first argument here.
-
So we could do title.
-
And then we can do one of two things here.
-
We can either specify three arguments or two arguments as a whole.
-
If we specify three,
-
the middle argument is going to be the comparison logic.
-
By default, if we specify just two arguments,
-
that'll be an equals check.
-
So we'll do equals there.
-
And then we'll provide in the value as the third argument.
-
So we'll do smells like teen spirit.
-
We can hit enter here to run that query.
-
And we're going to get back a proxied result of our model instance,
-
which is going to wrap up our attributes.
-
So we can actually see the underlying results that we got back.
-
For that reason, we'll end this with our Poyo,
-
which will return back the data as we're seeing inside of the database.
-
So I enter there and there we go. Okay, cool.
-
So we do indeed get back our smells like teen spirit result.
-
And if we remove that third argument,
-
we'll see that we get back the exact same thing
-
because it will default to just doing an equals check.
-
There we go. But maybe we want to do a likeness check instead.
-
So we could provide that in place as our middle argument there.
-
And then maybe we could do where the title starts with smells.
-
So we'll take this back, hit percent there to say anything can be after smells.
-
We'll run this and we get back the exact same result.
-
We could try switching this to something different.
-
So maybe love as I think we had love hangover as a result.
-
Yep, there we go.
-
And we get back love hangover as a result there because it starts with love.
-
We could do something similar.
-
So we could do where created at is greater than
-
or you could do equals to if you wanted to 2024, 02.
-
And maybe we'll do 24, which is a day before these results were created.
-
We hit enter there and we get back all three of our results
-
because we created all three movies on the same day.
-
If however, we switch this to 26 a day after they were created,
-
we'll get back no results.
-
But there's different variants to the where statement here as well.
-
So if we go back up a couple of steps to where we were doing our where like,
-
instead of providing three arguments to get to the like,
-
we could provide two and instead use the where like method,
-
where now the default comparison logic will be a like statement.
-
So if we run this, we'll get back our result as expected.
-
And there is also a where I like to do a case insensitive likeness check.
-
So for example, if we switch our love to a lowercase L,
-
hit enter there, we still get back a result.
-
But if we get rid of that I and the where I like,
-
we won't get back a result because there's no capitalization matches
-
for the love check here.
-
If we dive back into the loose documentation,
-
scroll back up to the select query builder statements,
-
there is a bunch of different variations of the where statement.
-
So you have where I like, where like, where, where column,
-
where in, where null, where exists, where between, where raw, where JSON.
-
And on top of each and every single one of those,
-
there is also an and and an or variation.
-
So for example, if we wanted to do where like title,
-
we'll switch this back to a capital L so that we actually have a match.
-
Starts with love and the status ID is one.
-
We could add in an and where,
-
specify the column as a status ID and the value as one, run that.
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!