Pagination and filtering in TypeScript and TypeORM

Question

How can i create an function to pagination and filtering with typeorm?

I use queryBuilder() but i don't how to create an function to divide the results into pages and result on one page.

I try like this:

  async getPaginatedAndFilteringUsers(dto: PaginationUserDto): Promise<User[]> {
    
    const user = this.conn.getRepository(Employee)
      .createQueryBuilder('user ')
      .orderBy('user.id', dto.order)
      .skip(dto.rowsPerPage)
      .take(dto.page);

    return user;
  }

but it not work.

I want to create an function with params like this: localhost:3000/user?page=1&rowsPerPage=15&orderBy=DESC

can someone tell me how can i do this with typeorm?

thanks a lot for all help :)

Answer 1

Firstly, I see that you did not execute the query. So, add the .getMany() at the end of the query chain:

getPaginatedAndFilteringUsers(dto: PaginationUserDto): Promise<User[]> {
    return this.conn.getRepository(Employee)
      .createQueryBuilder('user')
      .orderBy('user.id', dto.order)
      .skip(dto.rowsPerPage)
      .take(dto.page)
      .getMany();
}

---

Secondly, I have no idea what do you put in PaginationUserDto. I hope, that you put to it some users' info and pagination parameters like page, rowsPerPage and orderBy. If not, that's the second point to fix your issue: you need to parse query params and put it to your dto (because of you use these params from dto)

---

I hope it would be helpful

Answer 2

The best way to do this in pure typeOrm is as follows

async getPaginatedAndFilteringUsers(dto: PaginationUserDto): Promise
  < User[] > {
  const [user, count] = await User.findAndCount({
    order: { id: "DESC", },
    skip: dto.rowsPerPage,
    take: dto.page,
  });
  return paginatedUserInfo{
      user: user, 
      totalCount: count,
    };
}

The totalCount is not consider the paginated result it will return full row count