How To Apply A Dynamic Sort Filter To Your Query
In this lesson, we'll learn how we can add a user-selected sort option to our filters. Then, we'll apply the selected filter to our movie query
- Author
- Tom Gobich
- Published
- May 01
- Duration
- 7m 12s
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
How To Apply A Dynamic Sort Filter To Your Query
-
(upbeat music)
-
Okay, so next let's get a sort option added
-
into our filter fields here as well.
-
So let's hide our browser back away.
-
Let's go ahead and move this method into a service
-
to clean up our index method here a little bit.
-
So if we dive into our services,
-
we already have a movie service.
-
So let's go ahead and give this query here a copy,
-
jump into our movie service.
-
Most of what we have within here, if not everything,
-
is from when we were reading individual files.
-
So we can go ahead and get rid of all of the methods
-
that we have in here as they're no longer in use.
-
And it looks like we can get rid
-
of all of the imports there as well.
-
So let's add in a static method
-
to this service called getFiltered.
-
We already know it's for our movies
-
because we're inside of the movie service.
-
So we'll leave movies out of the title.
-
Then for right now, all that we need to do
-
is accept in our filters.
-
This will be a type of record,
-
string as the key and any as the value.
-
And then let's go ahead and paste our query inside of here.
-
We're gonna leave async off of this method
-
and we're not going to await it inside of here.
-
Instead, we'll just directly return back
-
and what this will do is instead return back
-
from this method, a type of model query builder.
-
Right now we don't have that
-
because we have some red squigglies to take care of first,
-
but by returning back from model query builder,
-
rather than the executed query,
-
which we'd get by awaiting this query,
-
we're gonna give ourselves the exact same looking logic
-
within our controller,
-
but we'll also provide ourselves the option
-
to expand off of the query within our controller
-
if needed as well.
-
Okay, so let's go ahead and do a find and replace
-
for our QS because we've now called this our filters.
-
Replace that.
-
And then we need to import our movie model.
-
So we can click anywhere within there
-
and hit command dot to open up our quick fix options
-
and click on add import from models movie.
-
Give it a save and everything should be back to happy.
-
Let's go ahead and jump into our movies controller.
-
And now we can select up to just our movie query,
-
leaving the cost movies equals await.
-
And we can now do movie service
-
and import that from our services movie service.
-
Since we made this a static method,
-
we don't need to instantiate a new instance of the service.
-
So we can just directly call get filtered,
-
providing in our query string filters.
-
So now let's define our sort options.
-
So let's jump back into our movie service
-
and let's do a static property on here.
-
So static sort options equals an array.
-
And inside of this array, we'll define objects.
-
For each of these objects, we'll have an ID
-
just so that we can uniquely identify the individual option
-
to use the field in the sort direction for the option
-
within our get filtered query.
-
So for our ID, let's first say we want to sort
-
by title ascending.
-
So we can do title as ascending there.
-
Then we'll do a text of title asc,
-
field title, and dir as asc.
-
So as you can see, we're just kind of building out
-
a select option that contains the ID value for the option,
-
the text value for the option,
-
and then the underlying field and direction
-
that we would use inside of our query
-
to add in the order by clause.
-
Okay, let's next do ID title descending,
-
text title descending,
-
field will be title, and dir will be descending.
-
So let's first get these two working
-
and showing up on our form.
-
And then we'll take a look at adding others as well.
-
So we have our const movies, movie statuses.
-
Now we need our const movie sort options equals,
-
and we can just grab that directly off
-
of our movie service as sort options
-
and pass that in here.
-
So movie sort options.
-
Now that we're passing that into our page state,
-
we can jump into our index page.
-
Let's give this form input here a copy
-
where we have our select and paste just below it.
-
We'll switch the label here to sort.
-
We'll go ahead and have the name be sort as well.
-
For this one, it's gonna need to sort by something.
-
So we'll go ahead and remove the all
-
and we'll loop over our movie sort options.
-
And we'll just normalize this field name into just item.
-
So item, item, filters dot,
-
and we call this field name sort.
-
So that's what it will be within our filters.
-
So we'll call it sort there as well.
-
And then from status to item there too.
-
Okay, so if we give this a save, jump into our browser,
-
we should now see a second select for our sort.
-
Okay, we have undefined and undefined.
-
And that actually makes sense
-
because the options that we built don't have a name,
-
but rather a text property.
-
And let's jump back into our browser once more.
-
And there we go.
-
So now we have title ascending and title descending.
-
If we switch our sort to title descending and hit search,
-
we see it get added into our filter fields,
-
sort equals title underscore descending.
-
So now we just need to add it into our movie query
-
so that it's actually used and applied.
-
So let's jump back into our movie service
-
where we are now passing it into our filters
-
because this is grabbing just straight
-
from our query strings.
-
So let's do const sort equals,
-
and we're either going to want to grab the applied item
-
out of our sort options by its ID,
-
matching whatever we have within our filters.sort field,
-
or default to the first sort options item.
-
So here we can do this sort options.find option
-
where option.id equals our filters.sort.
-
Otherwise, we'll just default to this sort options
-
and the first item.
-
Give that a save.
-
It'll break that down into a new line.
-
Let's give ourselves another new line there
-
just to split the two and okay.
-
So now we either find an item within our sort options
-
that's applied via our filter sort value,
-
or we'll just default to the first item
-
within our sort options.
-
So we should always have a value here for our order by.
-
So we could do sort.field and then sort.dir
-
for our direction.
-
And once we get here,
-
we're going to see that sort.dir is not happy
-
within our order by because the order by
-
specifically has a type of ascending,
-
descending, or undefined.
-
And if we take a look at our sort type,
-
you can see all of the values here are of type string.
-
So we'll need to normalize our sort options into a type.
-
So up here at the top of our service file,
-
let's do type movie sort option equals object.
-
Our ID of type string, text of type string.
-
And then for our field,
-
we can hone in on the types a little bit
-
and just do key of our movie model.
-
And then we have dir and we'll just have this match
-
the same types needed for our order by clause.
-
So we'll do ascending or descending or undefined.
-
Okay, with that defined,
-
we just want to apply that to our sort option as an array
-
for the type.
-
So type movie sort option array there.
-
All of our current members
-
already fulfill that type requirement.
-
So we're a-okay there.
-
And if we scroll down now, so too is our actual order by.
-
So we should be able to give this a save.
-
Jump back into our browser.
-
Let's go ahead and give the page here a refresh.
-
And there we go.
-
So we are now looking at the inverse side of our titles
-
where we're starting with,
-
it looks like W's going in the V and then T.
-
And then if we switch this back to title ascending,
-
give it a search.
-
We're now right back to the original items
-
that we've been looking at all along.
-
If we go back to our one filter,
-
so just those containing something to the sort of a one,
-
we're looking at that in alphabetical order there.
-
Switch now to descending
-
and there they are in the descending order.
-
So let's add one more property here to our sort options.
-
So we'll do an object here with ID released at,
-
and we'll do ascending text, release date,
-
and ascending here just to note for ourselves.
-
Field released at enter is ascending.
-
All right, we'll do the flip side.
-
So ID released at descending text,
-
release date, descending field,
-
release that, enter is now descending.
-
So we can give that a save,
-
jump back into our browser, give our page a refresh.
-
And now we should be able to sort these
-
by the release date ascending, just like so,
-
and the release date descending.
-
If we apply that to just those that have already released,
-
now we're seeing a list of movies
-
with one somewhere in the title with a status of released
-
from the most recently released to the oldest released.
-
And then if we switch to the ascending,
-
we will then see the oldest released
-
to the most recently released.
-
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
-
7.0The Flow of Middleware7m 49s
-
7.1Authenticating A Newly Registered User4m 14s
-
7.2Checking For and Populating an Authenticated User2m 10s
-
7.3Logging Out An Authenticated User2m 24s
-
7.4Logging In An Existing User6m 54s
-
7.5Remembering A User's Authenticated Session6m 55s
-
7.6Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
-
8.0Creating A Movie List Page3m 43s
-
8.1Filtering A Query By Pattern Likeness7m 9s
-
8.2Filtering Our List by Movie Status5m 47s
-
8.3How To Apply A Dynamic Sort Filter To Your Query7m 12s
-
8.4Joining SQL Tables To Order By A Related Column4m 49s
-
Validating Query String Filter Values7m 23s
-
How To Paginate Filtered Query Results9m 15s
-
Pagination First, Last, Next, and Previous Buttons4m 2s
-
-
User Watchlist
-
An Alternative Approach to Many-To-Many Relationships4m 56s
-
Toggling A Movie in an Authenticated User's Watchlist9m 56s
-
Listing and Filtering User Watchlist Items7m 34s
-
Allowing Users To Toggle A Movie As Watched4m 44s
-
Filtering By User's Watched Status6m 7s
-
Defining A Composite Unique Constraint4m 46s
-
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!