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

Share Improve this question Follow edited Feb 15, 2024 at 14:01 jonrsharpe 122k3030 gold badges268268 silver badges476476 bronze badges asked Jul 2, 2020 at 18:48 user13835196user13835196 1

• 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

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 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