How to execute a paginated query with TypeORM and Oracle 11g

execPaginatedQuery.ts

import { EntityManager, ObjectLiteral, SelectQueryBuilder } from 'typeorm'
import { RelationCountLoader } from 'typeorm/query-builder/relation-count/RelationCountLoader'
import { RelationIdLoader } from 'typeorm/query-builder/relation-id/RelationIdLoader'
import { RawSqlResultsToEntityTransformer } from 'typeorm/query-builder/transformer/RawSqlResultsToEntityTransformer'

export type PaginateInput = {
  page?: number
  perPage?: number
}

export async function execPaginatedQuery<T extends ObjectLiteral>(
  manager: EntityManager,
  getQuery: (query: SelectQueryBuilder<T>) => SelectQueryBuilder<T>,
  input: PaginateInput,
): Promise<Paginated<T>> {
  const subQuery = getQuery(manager.createQueryBuilder())

  const page = input.page || 1
  const perPage = input.perPage || 15
  const skip = (page - 1) * perPage + 1
  const limit = page * perPage

  const query = manager
    .createQueryBuilder()
    .from((subQuery) => {
      subQuery = getQuery(subQuery)

      const orderBys = subQuery.expressionMap.orderBys as unknown as Record<
        string,
        string
      >

      const orderByKeys = Object.keys(orderBys)

      if (orderByKeys.length) {
        const orders = orderByKeys.map((key) => {
          const direction = orderBys[key]

          return `${key} ${direction}`
        })

        const rowNum =
          'ROW_NUMBER() OVER (ORDER BY ' + orders.join(', ') + ') Row_Num'

        subQuery.addSelect(rowNum)
      } else {
        subQuery.addSelect('ROWNUM Row_Num')
      }

      return subQuery
    }, 'SUB')
    .where('SUB.Row_Num BETWEEN :skip AND :limit', {
      skip,
      limit,
    })

  const rawResults = await query.getRawMany()

  const relationIdLoader = new RelationIdLoader(
    manager.connection,
    manager.queryRunner,
    subQuery.expressionMap.relationIdAttributes,
  )

  const relationCountLoader = new RelationCountLoader(
    manager.connection,
    manager.queryRunner,
    subQuery.expressionMap.relationCountAttributes,
  )

  const rawRelationIdResults = await relationIdLoader.load(rawResults)
  const rawRelationCountResults = await relationCountLoader.load(rawResults)

  const transformer = new RawSqlResultsToEntityTransformer(
    subQuery.expressionMap,
    manager.connection.driver,
    rawRelationIdResults,
    rawRelationCountResults,
    manager.queryRunner,
  )

  const items: T[] = transformer.transform(
    rawResults,
    subQuery.expressionMap.mainAlias!,
  )
  
  let total: number | undefined

  if (page === 1) {
    total = await subQuery.getCount()
  }

  return {
    items,
    total,
    page,
    perPagina,
  }
}

execute.ts

async execute(input: PaginateInput, user: User) {
  return execPaginatedQuery<Material>(
    this.repository.manager,
    (query) => {
      query
        .select(['material.id', 'material.code', 'material.name'])
        .from(Material, 'material')
        .orderBy('material.code', 'ASC')

      this.applyFilters(query, input)

      return query
    },
    input,
  )
}

material.entity.ts

@Entity()
@Unique(['codigo'])
@Unique(['nome'])
export class Material extends BaseEntity {
  @PrimaryColumn({ length: 26 })
  id: string

  @Column({ type: 'int' })
  code: number
  
  @Column({ length: 255 })
  name: string
}

paginated.ts

export class Paginated<T> {
  items: T[]

  total?: number | null

  page: number

  perPage: number
}

Evidence:

Response BODY:

SQL Query LOG:

Implementation Usage (customized for Brazilian developers):