Pagination, Search and Sort

Intro

We are going to add pagination, search and sort functions to the previous api built with Nest.js, TypeORM and PostgresDB.

Pagination

For pagination, we need to add page: number and how many rows we want to pull from the database table and we are going to name it as take: number. So we need to add two more parameters to the findAll() method:

async findAll(page: number, take: number): Promise<Todo[]> {
    return await this.TodoRepository.find({ take, skip: take * (page - 1)})
}

And now, we need to add two more Query Parameters in todo controller so we can access the page and take(number of rows) via api/v1/todos route. And We might also want to set default values for page and take to prevent query errors in case we forget to put page and take values when making "GET" request.

@Get()
async find(@Query('page') page = 1, @Query('take') take = 5): Promise<Todo[]> {
    return await this.todoService.findAll(page, take)
}

Sorting (ASC, DESC)

For sorting, we need to add two more parameters(sort, order) to the findAll() method. sort will be used as key and order will be used as value for sorting query.

async findAll(page: number, take: number, sort: string, order: SortType): Promise<Todo[]> {
    let squery = {}
    switch (sort) {
        case 'updated_at':
            squery = { updated_at: order }
            break;
        case 'created_at':
            squery = { created_at: order }
            break;
        case 'title':
            squery = { title: order }
            break;
        case 'id':
            squery = { id: order }
            break;
        default:
            squery = { updated_at: SortType.D }
            break;
    }
    return await this.TodoRepository.find({ take, skip: take * (page - 1), order: squery })
}

Let's update the controller too.

@Get()
async find(@Query('page') page = 1, @Query('take') take = 5, @Query('sort') sort = "updated_at", @Query('order') order = SortType.D): Promise<Todo[]> {
    return await this.todoService.findAll(page, take, sort, order)
}

Yay!!! We have successfully added Pagination and Sorting to our /GET route.

 > api/v1/todos?page=1&take=3&sort=updated_at&order=ASC

Search

Implementing Search will be a bit different from Pagination and Sorting because it would be better to have a seperate route for search with its own search rows count for pagination. So we will be able to use Pagination for searched results too.

First, we need to create a new method in todo.service and let's name it search(). And since our Schema/Entity has six columns ( id, title, text, completed, created_at, updated_at) in total we might want to choose two string-based columns(title, text) out of it:

async search(title: string, text: string): Promise<Todo[]> {
    let query = {}
    if (title) {
        query = { ...query, title: ILike(`%${title}%`) }
    }
    if (text) {
        query = { ...query, text: ILike(`%${text}%`) }
    }
    return await this.TodoRepository.find({ where: [query] })
}

And, let's create a new route "/api/v1/todos/search" by adding a new method in the todo.controller with query parameters::(title, text),

@Get("search")
async search(@Query('title') title = null, @Query('text') text = null): Promise<Todo[]> {
    return await this.todoService.search(title, text)
}

Now, we should be able to search tasks/todos by title or text, for example:

 > api/v1/todos/search?title=Hello&text=Hello tasks

However, we might also want to have a seperate Pagination for searched results in frontend too. So let's add page and take parameters to the controller.service like the one we did for finAll() method:

async search(page: number, take: number, title: string, text: string): Promise<SearchResponse> {
    let query = {}
    if (title) {
        query = { ...query, title: ILike(`%${title}%`) }
    }
    if (text) {
        query = { ...query, text: ILike(`%${text}%`) }
    }
    const count = await this.TodoRepository.count({ where: [query] })
    const resp = await this.TodoRepository.find({ take, skip: take * (page - 1), where: [query] })
    return { data: resp, count }
}

Now we can update the search() method in the todo.controller too.

@Get("search")
async search(@Query('page') page = 1, @Query('take') take = 5, @Query('title') title = null, @Query('text') text = null): Promise<SearchResponse> {
    return await this.todoService.search(page, take, title, text)
}

And now, we should be able to make GET request via "/api/v1/todos/search". For example:

 > api/v1/todos/search?title=Hello&text=Hello tasks&page=1&take=5

Yay!!! Our Nest.js Todo API has pagination, search, sort fuctions 🎓!.

Source Code.

Go Back.