Reusable Query Statements with Model Query Scopes
In this lesson, we'll learn about Model Query Scopes and how we can use them to create easily reusable query statements that we can apply using the Model Query Builder.
- Author
- Tom Gobich
- Published
- Mar 11
- Duration
- 8m 11s
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
Reusable Query Statements with Model Query Scopes
-
[MUSIC]
-
Query scopes essentially allows us to define reusable query statements that we
-
can apply into any query that we need to use.
-
So if we dive into the documentation, we'll see an example right here.
-
It's provided as a static property where we call the scope and
-
provide it in a callback method that accepts the query.
-
We then run whatever query statements we want to have happen for
-
this query scope inside of this callback method.
-
And then we can make use of it using with scopes or
-
even apply on the model query builder itself.
-
Furthermore, you can also pass arguments into the query scope as well.
-
That comes through as the second argument within the callback function that we
-
provide into the scope function, just like so.
-
So here they're providing in the user where they can perform some check to see
-
whether or not they're administrator.
-
If they are, they kick back.
-
Otherwise, you perform some query action.
-
Note that you don't need to return back an instance of the query.
-
We're just building off of an already bound query property.
-
So any alterations or statements that we add on to query will get
-
automatically applied onto the query that we're building.
-
So a use case that we have that would be fantastic for
-
query scopes is checking to see whether or not a movie is actually released.
-
Because we have both a status ID and a released at.
-
We need to check and make sure that both of those are set for
-
a movie to be actually released.
-
Because the movie status could be set to released, but
-
the released at could be still future dated.
-
So we wanna make sure that the released at is somewhere in the past and
-
the movie status ID is set to released.
-
So for that, let's go ahead and hide our browser back away.
-
So we wanna jump up to the movie model.
-
And I like declaring these after all of the model properties.
-
And we'll provide this as static.
-
We'll call this released equals scope.
-
We'll import that from Adonis Lucid ORM, provide that in a callback function.
-
This callback function provides to us the underlying query,
-
where we just need to now build off of this query with the statements that we
-
want applied whenever we call this released query scope.
-
So for this released query scope, what we wanna do is first query.where
-
our status ID equals movie statuses.
-
And let's import that from our enums dot and set it to released.
-
On top of that, what we also wanna do is an additional where check,
-
where our released at is less than or equal to date time from luxen.now.
-
And we'll call to SQL, so that's SQL save.
-
There we go.
-
Give that a save and I don't like that format.
-
Okay, well, let's break that back down so that we can read it first.
-
Okay, so whenever we apply our released query scope,
-
we're gonna be adding in two where statements.
-
Where status ID equals released, so that's our ID of five,
-
as we can see right here.
-
And our released at date time is less than or equal to date time now.
-
So right now at this point in time.
-
Since we have multiple where statements being applied here and
-
we want this to be a definite and.
-
So where status ID and released at.
-
What I would prefer to do to future proof this is to nest this in parentheses.
-
So we can provide a callback function to our where, call query inside of there.
-
I'm gonna get rid of that end bracket and add it down to the end here.
-
Essentially wrapping those two where statements inside of parentheses and
-
for the underlying SQL that will be generated.
-
Looks like we got a red squiggly.
-
I think that's just cuz we have query used twice now.
-
Yeah, we'll just replace this with group.
-
There we go. But we also have one more determination.
-
Do we want null valued release stats to just default to using the status ID?
-
I think for our use case, let's go ahead and
-
make sure that release that's not null as well.
-
So let's do where not null and then that just accepts in the column name.
-
So released, yep, down here that should be released at there as well.
-
Okay, cool.
-
Essentially here if we add in some comment blocks down here,
-
what the underlying where statement here ends up looking like is, okay, so
-
we have select star from movies where, I'm gonna break the where down on a separate
-
line, and our where statement here is saying where we're wrapping these
-
statements inside of parentheses, so we'll have our parentheses here.
-
Status ID equals five and released at is not null and
-
released at is less than or equal to whatever now is.
-
So I'll use a little bit of pseudocode right there.
-
So essentially this is the SQL representation of what we have within
-
our query scope and the reason why we're wrapping these statements inside of
-
an additional parentheses as we are right here and right here is so that we can chain
-
additional statements to build additional and
-
where statements outside of the scope of our query scope here.
-
So let's go ahead and get rid of that and see how this works in practice.
-
So in order for it to work, we're gonna need some released movies.
-
We already have about, I think, ten not released movies.
-
So let's jump back down to our fake cedar.
-
We don't need additional users or synapse, so I'm just gonna comment that out.
-
And let's create maybe two movies that are released.
-
Now, within our factory, we have these set to,
-
I'm not entirely sure it was not released though, writing.
-
So what we wanna do is merge in alternative values.
-
So we can merge together an object and
-
this will override anything that we have set within our factory.
-
So we could say our status ID is set to movie statuses,
-
import that and set it to released.
-
And then our released at is we can say date, time, import that from Luxem.
-
Scroll down a little bit here and get rid of all these pop-ups.
-
And we'll just set it to now and then let's subtract or
-
looks like the method's minus and this accepts a duration like value as an object.
-
So we could say month one to subtract one month from this date time.
-
Let's give that a save, that's gonna do some formatting.
-
And let's go ahead and run this cedar to create two movies that are released.
-
So we'll jump back into our terminal here, node, ace, db.
-
And we've recently run this, so I'm gonna hit up and there it is.
-
So I'm gonna go ahead and run that, there we go.
-
I'm gonna go ahead and hide our text editor back away and
-
let's enter in a rebel session.
-
So node, ace, rebel and let's await load models.
-
Await models.movie.query and we have two options whenever it comes to actually
-
applying a query scope into our query builder statement.
-
We could do with scopes, which is a little bit more direct with what we're doing.
-
And then this will provide us our scopes as a callback function.
-
So we can do scopes. and then call the function released as we have defined in
-
the model to apply this query scope to this query builder statement.
-
So if we run here, we should get back, let's add Poyo to that so
-
that we can actually read the properties.
-
So .poyo there, there we go.
-
We should get back just the movies that are explicitly released,
-
which we have two of.
-
We see the status ID is five for both of these.
-
And both these titles I don't recognize going through quite yet so far.
-
So these are two brand new movies that we haven't seen yet and both are released.
-
We take a look at the release that and that is a month ago.
-
So cool, that looks all right.
-
Everything there is working okay.
-
Now if we go up, the second approach that we have to apply query scopes is instead
-
of doing with scopes, there's an alias method just called apply.
-
It's shorter but not as direct with what exactly we're doing.
-
But it is documented so if somebody were to come along curious what exactly it is,
-
they'd be able to look in the documentation and see exactly what it does.
-
So this does the exact same thing.
-
It provides us an instance of our scopes where we can call any query scopes that
-
we have to find on the model as a method inside of this callback.
-
So if we run this, we're going to get back the exact same thing.
-
And this just allows us to build out additional statements as needed and
-
reuse this query scope over and over again in as many queries as we need.
-
And for our particular use case of checking to see whether or
-
not a movie is released, this will just help us keep our queries clean,
-
a little bit more legible in what they're doing, and
-
make the overall queries to get to released movies much easier to work with.
-
So we can build out additional where statements off of this as well.
-
So maybe where title is, let's do 16 tons.
-
It's a little bit shorter than the other one.
-
So 16 tons, just like so.
-
And we should just get back this ID 8 record right here.
-
And we get exactly that.
-
So you can continue building your queries however you need.
-
Just be cognizant where you've defined your query scope.
-
So if we scroll back up to our movie query scope,
-
how you've defined the statements within here.
-
For example, we've wrapped these within parentheses for a very specific reason so
-
that we can do additional or statements if we needed to
-
outside the realms of this particular scope.
-
So that's query scopes in a nutshell.
-
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! (4 Comments)
Please sign in or sign up for free to join in on the dicussion.
redeemefy
My assumption to this lesson is that the
models.movies.query()is injectingSELECT * FROM movies…by default and the scope is injecting the rest you created in the model? No where in the model or its seen thatSELECT * FROM movies…its written anywhere.Please sign in or sign up for free to reply
tomgobich
Yes, the model query builder will always provide a query scoped specifically to the model's table. If you don't specify a
selectwithin the query you're building, it will default to selecting all columns, which can be denoted by*. So, some examples:Please sign in or sign up for free to reply
redeemefy
Interesting….
I'm coming from writing APIs with NestJS and this framework indeed is taking a different but interesting approach.
Please sign in or sign up for free to reply
tomgobich
I'm not very familiar with NestJS so I, unfortunately, can't provide any direct comparisons, but under the hood, AdonisJS' query builder, for both the models and the database module, is a wrapping of the KnexJS query builder.
So, you do have access to an unrestricted query builder as well via the database query builder should you need it:
Just note that, unlike the model query builder, since the database query builder isn't going through models, naming strategies won't go into effect so you'll need to use the column names as they're defined inside the database.
Then, you also have the static model query methods as well for more straightforward queries.
Please sign in or sign up for free to reply