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.
-
And now we're going to get back results where the title starts with love
-
and where the status ID is one.
-
Now, the default behavior,
-
whenever you chain multiple where statements together
-
is going to be to do an and check.
-
So we could just run where here again,
-
and it will be the exact same thing as our results before,
-
but there's also an or variation as well.
-
So we could do or where status ID is one.
-
All of our movies currently have a status ID of one.
-
So because of this, we're going to get back all three movies
-
as our results here, because they all have a status ID of one.
-
And we ran where title starts with love or where the status ID is one.
-
If we want to just two results back, we could limit the results.
-
So let's go back to our previous query.
-
Dot limit to, to limit the results to just two.
-
So if we hit enter there, now we just have two results that came back.
-
The third one was omitted and we can order these as well.
-
So we can do order by, maybe created at,
-
we can either do ascending or ASC, which will be the default sort,
-
or we can do descending to go in the inverse.
-
So ascending is going to be an alphabetical order.
-
It's going to be A to Z, one to 100, the first to the 30th.
-
Descending would then be the inverse order.
-
So it would be Z to A, 100 to one and the 30th to the first.
-
So if we go ahead and run this in descending order,
-
they were all created in almost the exact same time.
-
We look at the very last character here,
-
we're going to see a very slight difference.
-
The first result that we got back was dot 761 Zulu time.
-
And the second result that we limited to was dot 760.
-
So very, very slight difference,
-
but it is indeed going in descending order.
-
If we switch this now to ascending, so ASC,
-
or that'll be default so we can get rid of that argument there altogether.
-
Hit enter. A little bit bigger of a difference here.
-
We can see we have 754 Zulu time as our first argument,
-
and then it's going upwards to 760 Zulu time.
-
So now we're going in ascending order based off of our created at.
-
So there's just way too much to be able to cover everything
-
with these query builders.
-
But there's a couple of things that I do want to touch on
-
because they're very useful.
-
So first, you can pass a callback argument to your where statement,
-
and that will essentially wrap that statement in parentheses
-
in terms of the underlying SQL that's generated.
-
For example, we can await models dot movie dot query
-
to get our query builder for a movie dot where.
-
Let's say status ID is one, which matches all of our results.
-
We can do an additional where statement to say,
-
and pass in a callback function to now do query dot where
-
within parentheses here.
-
And we can now do ID is one or where ID is two.
-
So if we run this, we didn't add Poyo on there.
-
So we're getting back. Let's do that real quick.
-
So Poyo so that we can actually see the objects.
-
Run that. Now we get back two results where the ID is one and two.
-
All three of our movies have a status ID of one.
-
So all three match this result.
-
But now we have a separate parenthesized statement
-
where the movie has an ID of one or two.
-
If we run that again, but change our first where statement slightly
-
to something that just one of these results matches.
-
So maybe where the title starts with streets.
-
So we can do where like switch our column to title and our value to streets
-
with a percent sign afterwards to say that anything can come after streets.
-
Run this. We'll just get back one result where the title starts with streets
-
and the ID is either one or two.
-
And we're getting back just the one result here because the callback function
-
that we're providing to the where statement here is wrapping our where
-
and our or where in parentheses.
-
So it needs to match one of these two and our where like,
-
which is different than if we just did wait models, movie query,
-
where like title starts with streets, where ID is one or where ID is two.
-
Actually, let's switch the or where there, make it a little bit more evident
-
where ID is two or one.
-
And we'll do OYO so that we can actually see the results.
-
Run that. And now you see that we get back both results
-
because this statement is different than this one.
-
This one's wrapping this where and or where for our ID check in parentheses.
-
So it needs to match either ID of one or two and a title that starts with streets.
-
Whereas this one has our second where statement outside of parentheses.
-
So it needs to have a title starting with streets and an ID of two or just an ID of one.
-
So you can see how the context kind of changes there depending on whether or not
-
we're providing our where statement inside of a callback,
-
which will wrap it in parentheses or just as a straight chain.
-
Additionally, we can do an if check to conditionally apply a where as well.
-
So let's do let title filter equal an empty string.
-
We'll define that. So now if we print out title filter, we'll get back an empty string.
-
Cool. So we can do a wait models, movie dot query to access the query builder.
-
And instead of just doing where title has the value of our title filter,
-
which will return back in the results because we don't have any titles that are an empty string,
-
we can instead provide this where statement inside of an if check.
-
So we do if title filter provide the second argument as a callback function query, just like so.
-
With this if check, if title filter is truthy,
-
it will run the first argument that we have that callback.
-
Otherwise, we can add in an else statement as well as a third argument
-
that's also a callback to do query dot where something else.
-
Don't really have anything to do an else for in this particular use case.
-
So we'll run this and you'll see that we get back all three of our results.
-
But if we switch title filter to equal love hangover, which is one of our movies,
-
and we try to run that query again, you'll see that we just get back love hangover.
-
We can't see it, but if we add Poyo to the end of that,
-
we'll be able to see that we do indeed just get back love hangover.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 23s
-
2.1Rendering a View for a Route6m 29s
-
2.2Linking Between Routes7m 51s
-
2.3Loading A Movie Using Route Parameters9m 17s
-
2.4Validating Route Parameters6m 6s
-
2.5Vite and Our Assets6m 38s
-
2.6Setting Up Tailwind CSS9m 5s
-
2.7Reading and Supporting Markdown Content4m 32s
-
2.8Listing Movies from their Markdown Files8m 51s
-
2.9Extracting Reusable Code with Services7m 4s
-
2.10Cleaning Up Routes with Controllers4m 52s
-
2.11Defining A Structure for our Movie using Models9m 38s
-
2.12Singleton Services and the Idea of Caching6m 11s
-
2.13Environment Variables and their Validation4m 16s
-
2.14Improved Caching with Redis10m 44s
-
2.15Deleting Items and Flushing our Redis Cache6m 46s
-
2.16Quick Start Apps with Custom Starter Kits6m 28s
-
2.17Easy Imports with NodeJS Subpath Imports8m 40s
-
-
Building Views with EdgeJS
-
3.0EdgeJS Templating Basics8m 49s
-
3.1HTML Attribute and Class Utilities6m 9s
-
3.2Making A Reusable Movie Card Component10m 24s
-
3.3Component Tags, State, and Props4m 53s
-
3.4Use Slots To Make A Button Component6m 56s
-
3.5Extracting A Layout Component5m 13s
-
3.6State vs Share Data Flow2m 59s
-
3.7Share vs Global Data Flow6m 7s
-
3.8Form Basics and CSRF Protection6m 13s
-
3.9HTTP Method Spoofing HTML Forms3m 3s
-
3.10Easy SVG Icons with Edge Iconify7m 57s
-
-
Database and Lucid ORM Basics
-
4.0Configuring Lucid and our Database Connection4m 3s
-
4.1Understanding our Database Schema9m 35s
-
4.2Introducing and Defining Database Migrations18m 35s
-
4.3The Flow of Migrations8m 28s
-
4.4Introducing Lucid Models5m 43s
-
4.5Defining Our Models6m 49s
-
4.6The Basics of CRUD11m 56s
-
4.7Defining Required Data with Seeders11m 11s
-
4.8Stubbing Fake Data with Model Factories13m 48s
-
4.9Querying Our Movies with the Query Builder15m 30s
-
4.10Unmapped and Computed Model Properties3m 24s
-
4.11Altering Tables with Migrations7m 6s
-
4.12Adding A Profile Model, Migration, Factory, and Controller2m 57s
-
4.13SQL Parameters and Injection Protection9m 19s
-
4.14Reusable Query Statements with Model Query Scopes8m 11s
-
4.15Tapping into Model Factory States9m 15s
-
4.16Querying Recently Released and Coming Soon Movies4m 59s
-
4.17Generating A Unique Movie Slug With Model Hooks7m 59s
-
-
Lucid ORM Relationships
-
5.0Defining One to One Relationships Within Lucid Models5m 49s
-
5.1Model Factory Relationships2m 54s
-
5.2Querying Relationships and Eager Vs Lazy Loading5m 17s
-
5.3Cascading and Deleting Model Relationships5m 16s
-
5.4Defining One to Many Relationships with Lucid Models6m 56s
-
5.5Seeding Movies with One to Many Model Factory Relationships5m 24s
-
5.6Listing A Director's Movies with Relationship Existence Queries8m 41s
-
5.7Listing and Counting a Writer's Movies8m 41s
-
5.8Using Eager and Lazy Loading to Load A Movie's Writer and Director5m 18s
-
5.9Defining Many-To-Many Relationships and Pivot Columns9m 48s
-
5.10Many-To-Many Model Factory Relationships4m 50s
-
5.11A Deep Dive Into Relationship CRUD with Models18m 5s
-
5.12How To Create Factory Relationships from a Pool of Data13m 55s
-
5.13How To Query, Sort, and Filter by Pivot Table Data9m 47s
-
-
Working With Forms
-
6.0Accepting Form Data12m 15s
-
6.1Validating Form Data with VineJS9m 29s
-
6.2Displaying Validation Errors and Validating from our Request7m 16s
-
6.3Reusing Old Form Values After A Validation Error2m 3s
-
6.4Creating An EdgeJS Form Input Component5m 28s
-
6.5Creating A Login Form and Validator5m 1s
-
6.6How To Create A Custom VineJS Validation Rule9m 7s
-
-
Authentication & Middleware
-
The Flow of Middleware7m 49s
-
Authenticating A Newly Registered User4m 14s
-
Checking For and Populating an Authenticated User2m 10s
-
Logging Out An Authenticated User2m 24s
-
Logging In An Existing User6m 54s
-
Remembering A User's Authenticated Session6m 55s
-
Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!