How can I create a function for pagination and filtering with typeorm?

I am using the queryBuilder(), But I don't know how to create a function to divide the results into pages and results on one page.

Here's what I've tried so far but it does not work:

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;
}

I want to create a function for query parameters like this:

localhost:3000/user?page=1&rowsPerPage=15&orderBy=DESC

How can I do this with TypeORM?

How can I create a function for pagination and filtering with typeorm?

I am using the queryBuilder(), But I don't know how to create a function to divide the results into pages and results on one page.

Here's what I've tried so far but it does not work:

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;
}

I want to create a function for query parameters like this:

localhost:3000/user?page=1&rowsPerPage=15&orderBy=DESC

How can I do this with TypeORM?

• javascript
• typescript
• nestjs
• typeorm

• You should give more context to the people. What do you mean by "it not work"? Did it returned less than you expected? Show the raw query generated by function call. – Gompro Commented Jul 3, 2020 at 6:54

2 Answers 2

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

The best way to do this in pure TypeORM is as follows:

const getPaginatedAndFilteringUsers = async (
  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 considered the paginated result it will return a full row count