Implementing pagination in NestJS with TypeORM: A comprehensive guide

Question

Implementing pagination in NestJS with TypeORM: A comprehensive guide

Instead of running two separate queries, is there a way to retrieve the total count and records together in a single query?

If combining them is not possible, is there a method to reuse the where condition in both queries?

async findAll(query): Promise<Paginate> {
  const take = query.take || 10
  const skip = query.skip || 0
  const keyword = query.keyword || ''

  const builder = this.userRepository.createQueryBuilder("user")
  const total = await builder.where("user.name like :name", { name: '%' + keyword + '%' }).getCount()
  const data = await builder.where("user.name like :name", { name: '%' + keyword + '%' }).orderBy('name', 'DESC').skip(skip).take(take).getMany();

  return {
    data: data,
    count: total
  }
}

{
  count: 10,
  data: [
    {
      id: 1,
      name: 'David'
    },
    {
      id: 2,
      name: 'Alex'
    }]
}

typescript nestjs typeorm

Answer 1

Answer №1

If you're looking for some great examples, check out this project. One standout feature of typeorm is its handy method called findAndCount, which is perfect for scenarios like this.

async findAll(query): Promise<Paginate> {
    const take = query.take || 10
    const skip = query.skip || 0
    const keyword = query.keyword || ''

    const [result, total] = await this.userRepository.findAndCount(
        {
            where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
            take: take,
            skip: skip
        }
    );

    return {
        data: result,
        count: total
    }
}

You can access the Repository API here. For more information on the Repository class, refer to the documentation here.

Answer 2

If you're looking for some great examples, check out this project. One standout feature of typeorm is its handy method called findAndCount, which is perfect for scenarios like this.

async findAll(query): Promise<Paginate> {
    const take = query.take || 10
    const skip = query.skip || 0
    const keyword = query.keyword || ''

    const [result, total] = await this.userRepository.findAndCount(
        {
            where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
            take: take,
            skip: skip
        }
    );

    return {
        data: result,
        count: total
    }
}

You can access the Repository API here. For more information on the Repository class, refer to the documentation here.

Answer 3

Answer №2

in conclusion...

This special software checks for the presence of specified take and skip parameters in the URL. If they are found, it converts them from strings to numbers. If not specified, default values of 10 for take and 0 for skip are used.

The take parameter determines the number of results displayed per page, while skip indicates the starting point for reading records.

By setting up this middleware, I am able to intercept requests made to the "product/paged" route specifically for the GET method.

This allows me to extract these values in the controller and pass them on to TypeORM or an SQL query.

https://i.sstatic.net/fAZAv.png

@Injectable()
export class PagerMiddleware implements NestMiddleware {
  use(req: any, res: any, next: () => void) {
    req.query.take = +req.query.take || 10;
    req.query.skip = +req.query.skip || 0;
    next();
  }
}

implementation in module.

export class AdminFeatureApi implements NestModule {
  configure(consumer: MiddlewareConsumer) {
    consumer.apply(PagerMiddleware)
    .forRoutes({ path: 'product/paged', method: RequestMethod.GET })
  }
}

Controller

@Controller('product')
export class TrainingDomainController {
  constructor(private service: YourService) {}

  @Get('paged')
  get(@Query() { take, skip }) {
    return this.service.findAll(take, skip);
  }
}

and the service component

@Injectable()
export class YourService {
  constructor(
    @InjectRepository(YourEntity)
    private readonly repo: MongoRepository<YourEntity>
  ) {}

  async findAll(take: number = 10, skip: number = 0) {
    const [data, total] = await this.repo.findAndCount({ take, skip });
    return { data, total };
  }
}

satisfactory?

Answer 4

in conclusion...

This special software checks for the presence of specified take and skip parameters in the URL. If they are found, it converts them from strings to numbers. If not specified, default values of 10 for take and 0 for skip are used.

The take parameter determines the number of results displayed per page, while skip indicates the starting point for reading records.

By setting up this middleware, I am able to intercept requests made to the "product/paged" route specifically for the GET method.

This allows me to extract these values in the controller and pass them on to TypeORM or an SQL query.

https://i.sstatic.net/fAZAv.png

@Injectable()
export class PagerMiddleware implements NestMiddleware {
  use(req: any, res: any, next: () => void) {
    req.query.take = +req.query.take || 10;
    req.query.skip = +req.query.skip || 0;
    next();
  }
}

implementation in module.

export class AdminFeatureApi implements NestModule {
  configure(consumer: MiddlewareConsumer) {
    consumer.apply(PagerMiddleware)
    .forRoutes({ path: 'product/paged', method: RequestMethod.GET })
  }
}

Controller

@Controller('product')
export class TrainingDomainController {
  constructor(private service: YourService) {}

  @Get('paged')
  get(@Query() { take, skip }) {
    return this.service.findAll(take, skip);
  }
}

and the service component

@Injectable()
export class YourService {
  constructor(
    @InjectRepository(YourEntity)
    private readonly repo: MongoRepository<YourEntity>
  ) {}

  async findAll(take: number = 10, skip: number = 0) {
    const [data, total] = await this.repo.findAndCount({ take, skip });
    return { data, total };
  }
}

satisfactory?

Answer 5

Answer №3

My preference is to use pages instead of skipping directly

Here's an example endpoint: /users?page=4&take=3

async findAll(query): Promise<Paginate> {
    const take = query.take || 10
    const page=query.page || 1;
    const skip= (page-1) * take ;
    const keyword = query.keyword || ''

    const [result, total] = await this.userRepository.findAndCount(
        {
            where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
            take: take,
            skip: skip
        }
    );

    return {
        data: result,
        count: total
    }
}

2/. A better way to handle the response:

 async findAll(query): Promise<Paginate> {
     const take= query.take || 10
     const page=query.page || 1;
     const skip= (page-1) * take ;
     const keyword = query.keyword || ''

     const data = await this.userRepository.findAndCount(
         {
             where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
             take: take,
             skip: skip
         }
     );
     return paginateResponse(data ,page,take)

 }

export function paginateResponse(data,page,limit) {
  const [result, total]=data;
  const lastPage=Math.ceil(total/limit);
  const nextPage=page+1 >lastPage ? null :page+1;
  const prevPage=page-1 < 1 ? null :page-1;
  return {
    statusCode: 'success',
    data: [...result],
    count: total,
    currentPage: page,
    nextPage: nextPage,
    prevPage: prevPage,
    lastPage: lastPage,
  }
}

Answer 6

My preference is to use pages instead of skipping directly

Here's an example endpoint: /users?page=4&take=3

async findAll(query): Promise<Paginate> {
    const take = query.take || 10
    const page=query.page || 1;
    const skip= (page-1) * take ;
    const keyword = query.keyword || ''

    const [result, total] = await this.userRepository.findAndCount(
        {
            where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
            take: take,
            skip: skip
        }
    );

    return {
        data: result,
        count: total
    }
}

2/. A better way to handle the response:

 async findAll(query): Promise<Paginate> {
     const take= query.take || 10
     const page=query.page || 1;
     const skip= (page-1) * take ;
     const keyword = query.keyword || ''

     const data = await this.userRepository.findAndCount(
         {
             where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
             take: take,
             skip: skip
         }
     );
     return paginateResponse(data ,page,take)

 }

export function paginateResponse(data,page,limit) {
  const [result, total]=data;
  const lastPage=Math.ceil(total/limit);
  const nextPage=page+1 >lastPage ? null :page+1;
  const prevPage=page-1 < 1 ? null :page-1;
  return {
    statusCode: 'success',
    data: [...result],
    count: total,
    currentPage: page,
    nextPage: nextPage,
    prevPage: prevPage,
    lastPage: lastPage,
  }
}

Answer 7

Answer №4

If you're interested, check out this resource for NestJS and TypeORM integration:

https://github.com/nestjsx/nestjs-typeorm-paginate

Answer 8

If you're interested, check out this resource for NestJS and TypeORM integration:

https://github.com/nestjsx/nestjs-typeorm-paginate

Answer 9

Answer №5

There are two ways to achieve this: the first is by using createQueryBuilder and the second is with findAndCount

const userRepository = dataSource.getRepository(User);
const _take = query.take || 10;
const _skip = query.skip || 0;

Using createQueryBuilder

const qb = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .orderBy("user.id", "DESC")
    .take(_take)
    .skip(_skip);

    const users = await qb.getMany();
    const total = await qb.getCount();

Alternatively, you can use findAndCount. This method allows you to retrieve the total results in a single call.

const [users, total] = await userRepository.findAndCount({
        order: {
            id: 'DESC'
        },
        skip: _skip,
        take: _take
});

Answer 10

There are two ways to achieve this: the first is by using createQueryBuilder and the second is with findAndCount

const userRepository = dataSource.getRepository(User);
const _take = query.take || 10;
const _skip = query.skip || 0;

Using createQueryBuilder

const qb = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .orderBy("user.id", "DESC")
    .take(_take)
    .skip(_skip);

    const users = await qb.getMany();
    const total = await qb.getCount();

Alternatively, you can use findAndCount. This method allows you to retrieve the total results in a single call.

const [users, total] = await userRepository.findAndCount({
        order: {
            id: 'DESC'
        },
        skip: _skip,
        take: _take
});

Answer 11

Answer №6

When dealing with a large number of records that require pagination, such as during a data migration or bulk update process.

async retrievePaginatedData(query: any, transactionManager?: EntityManager): Promise<any> {

}

Answer 12

When dealing with a large number of records that require pagination, such as during a data migration or bulk update process.

async retrievePaginatedData(query: any, transactionManager?: EntityManager): Promise<any> {

}

Implementing pagination in NestJS with TypeORM: A comprehensive guide

Answer №1

Answer №2

implementation in module.

Controller

and the service component

Answer №3

Answer №4

Answer №5

Answer №6

Similar questions

how can JavaScript be used to retrieve an object based on a condition from an array of objects and an ArrayList

Attempting to quiet TS2339 through the utilization of the suppressExcessPropertyErrors compiler setting

Angular service is able to return an Observable after using the .then method

What is the best way to retrieve JSON data from a raw.github URL and save it into a variable?

Error: AppModule requires an array of arguments in order to function properly

What is the reasoning behind triggering ValueChanges during initialization even when there are no changes detected?

Failing Cypress component test: When leveraging an index.ts file for importing and exporting services

A guide on simulating childprocess.exec in TypeScript

I possess a dataset and desire to correlate each element to different elements

The parameter cannot be assigned to type 'HTMLCanvasElement | null' due to conflicting arguments

Encountering problem with '@datadog/browser-rum' compilation related to the 'allowedTracingOrigins' attribute

NESTJS Alert: Unable to identify a GraphQL output type for the specified entity. Ensure that your class has the correct decorator applied to it

Updating pointers to elements depending on their current status

Unable to simulate a static method in Vitest - encountering an error stating "is not a function"

When compiling TypeScript code with Google API libraries, the error TS2304 occurs stating "Unable to locate the identifier 'Long'"

Automatically injecting dependencies in Aurelia using Typescript

Error found in ngrx/effects with Typescript: the name used for a computed property must be associated with a predefined symbol

"What is the best way to determine the data type of an object retrieved from an API in TypeScript

Sharing the input value with a service in Angular 4

document.addEventListener versus $().on