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.

Created by: @tomgobich
Published: 1 year ago

Join the Discussion 0 comments

Be the first to comment!

Transcript

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.

Let's Learn AdonisJS 5

29 Lessons

5h 3m

Introduction

Routing

Controllers

Service Providers

Service Providers & The IoC Container

18m 45s

Lucid ORM

Query Builder

Stop losing your best ideas!

Use our integrated note feature to capture and organize your key insights. Take a note at any timestamp and jump instantly back to that moment in the video with a single click.