Limiting, Sorting, & Pagination with the Query Builder
In this lesson, we'll take a look at the various options we have to limit results when using the query builder including first, firstOrFail, limit, and pagination. We'll also learn how we can sort our results.
- Author
- Tom Gobich
- Published
- Dec 08, 23
- Duration
- 4m 57s
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
Limiting, Sorting, & Pagination with the Query Builder
-
(dramatic music)
-
Now, most times you're not gonna want your query
-
to actually return back every single record
-
that you have for a model.
-
So today we're gonna take a look
-
at how we can limit those records.
-
So let's take this back a little bit
-
to where we would get back to results.
-
So let's give what we have here a save
-
and jump back into Insomnia, send this off.
-
And you can see our fine minis returning back
-
both JavaScript and TypeScript at this point.
-
If we wanted to limit this to just the first matching result,
-
what we can do is call just first here.
-
So let's give that a save.
-
And now JavaScript comes first in our array.
-
So we're going to get that back as our result.
-
An additional thing to note here
-
is that we're no longer getting back an array
-
as our response.
-
Instead, we're just getting back the model itself
-
because we're expecting just that single result.
-
Now, if we switch this to, let's add an additional where,
-
ID, and let's use an ID that doesn't exist.
-
So let's do three.
-
If we run this again, since we're just using first,
-
if it doesn't match a result, it's going to return back null.
-
This null response matches within our type as well.
-
So if we take a look at the type of our fine mini,
-
it's going to be topic or null.
-
If we need this query to return back a response,
-
otherwise fail out, we can add or fail to our first method.
-
And now if we send this off,
-
if we don't get back a response,
-
we're going to get it back a row not found,
-
four or four error.
-
So in addition to just using first, first, or fail,
-
if you need to limit your results,
-
but not necessarily to just the first record,
-
there's also a limit method.
-
This limit method allows you to specify
-
any number that you need,
-
and then it will cut it off at that response number.
-
So for example, we have two results here.
-
If we cut it off at one and we get rid of our ID there,
-
and we run this again,
-
we're going to get back just that one result.
-
If we switch this to two,
-
let's get rid of our where statement there.
-
Now we're just limiting it to two as a whole, send this off.
-
Now we're getting back just two,
-
but instead of limiting it to just those with script,
-
we're now also getting back AdonisJS.
-
So TypeScript is getting cut off from our response,
-
which then allows us to set this to three,
-
which would then return back all three.
-
Now, in most cases, if you're going to limit your results,
-
you probably also want to sort your results as well.
-
We can do that easily by chaining off
-
of our query builder order by.
-
First argument is going to be the column,
-
so let's order by the name.
-
And then the second argument is optional,
-
but it's going to be the direction.
-
So it can either be ascending or descending.
-
Let's leave it off for now,
-
and we'll see that it will default to doing ascending.
-
So we get back our results in alphabetical order,
-
which just happens to match the default sort.
-
But if we switch this to descending,
-
you'll see that the order is then flipped.
-
So we get TypeScript, JavaScript, and then Adonis.
-
And now if we limit it to two,
-
instead of TypeScript being cut off,
-
AdonisJS will now be cut off.
-
You can also order by something like a date/time.
-
So let's do createdAt here, descending.
-
And now the results here will just happen
-
to match what we had previously
-
because that's the order that we inserted them in.
-
Let's go ahead and get rid of our limit
-
so that we can take a look at all three.
-
And you'll see that the createdAt is indeed,
-
we created these all on the 28th,
-
but the time here is 11/28 for that one,
-
11/23 for that one, and 11/21 for this one.
-
So it's ordering them from newest to oldest
-
whenever we do descending there.
-
If we switch this to ascending, send this off,
-
you'll see that it's now doing oldest to newest.
-
Now, in addition to first and limiting,
-
you can also limit your orders via pagination.
-
And to help you out with that,
-
there is a pagination helper.
-
For that, we can do .paginate,
-
specify the current page number that we're on.
-
So this would be one
-
since we don't have any pages currently.
-
Specify the number that we want per page.
-
Let's say two so that we actually have two pages
-
to paginate here.
-
And let's send that out.
-
So it's going to order them by the createdAt date
-
in ascending order,
-
and then it's going to paginate them.
-
It will get back the first page of results,
-
and it will limit the results to two.
-
In addition to that, whenever we send this off,
-
you'll see that it also comes
-
with a number of different properties
-
that help us with the actual pagination rendering
-
whenever it comes time to do that.
-
So we get back meta information
-
containing the total per page, current page,
-
last page, first page, as well as the URLs for this.
-
Then we have our data array,
-
which contains the actual response data for the page.
-
If we come back into our paginate method,
-
switch this to page two,
-
give this a save.
-
Where we have AdonisJS and JavaScript,
-
we should now expect to just see TypeScript.
-
And you'll see our meta information updates
-
to reflect that we're now on page two,
-
and our data is now reflected
-
to just the second page of data.
-
Now, if we were actually going to render out
-
the meta information here,
-
this first page URL and last page URL,
-
you can see is going slash with a query string of page,
-
and then the number for the actual page action.
-
We can alter what this page is by doing findMany.
-
And then we can set the base URL for the paginated records
-
by specifying that URL in.
-
So for example, we can do slash topics,
-
give this a save, run this again.
-
And now you can see our first page URL is slash topics
-
with the query string of the applicable page.
-
So that's updated accordingly.
-
If you happen to have a route defined,
-
in our case, we have topics.index here that we can use.
-
We can go ahead and specify that route
-
using the route module.
-
So we can do route.makeURL,
-
and then specify topics.index there.
-
And then we'll use that accordingly.
-
Give this a send, and we get back the exact same result.
-
Introduction
-
Routing
-
2.0Routing Introduction6m 14s
-
2.1Dynamic Routing with Route Parameters14m 1s
-
2.2Moving & Organizing Routes4m 19s
-
2.3Naming, Grouping, & Prefixing Routes8m 28s
-
2.4Multi-File Route Grouping Strategies5m 31s
-
2.5Generating URLs and Signed URLs13m 50s
-
2.6Extending the Adonis Router and Route Matchers15m 44s
-
2.7The Middleware Mountain12m 44s
-
-
Controllers
-
Service Providers
-
Lucid ORM
-
5.0Introducing, Installing, and Configuring Lucid ORM10m 0s
-
5.1Migrations & Understanding the Flow of Migrations16m 35s
-
5.2What To Know Before Altering Your Database with Migrations10m 26s
-
5.3What Is A Model?12m 44s
-
5.4Database Schema to Migration11m 56s
-
5.5Database Migrations To Lucid Models4m 59s
-
5.6Defining Model Relationships13m 4s
-
5.7Creating Records with Lucid ORM12m 38s
-
5.8Password Hashing & Model Hook Overview8m 42s
-
5.9Easy Querying with Static Model Query Methods7m 41s
-
-
Query Builder
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!