ppetzold/nestjs-paginate

Pagination and filtering helper method for TypeORM repositories or query builders using Nest.js framework :book::paperclip:

nestjs-paginate's Issues

Query dependent tables of a repository

I have a many to many relation between two tables (Customer, Documents)
@manytomany(() => Document)
@jointable()
documents: Document[];

Now I want to get all Documents by Customer paginated. Is there a smart solution for this case?

My workaround is to create the reference table by myself and use the repository of the reference table.

ORDER BY DOES`NT WORK

I try to do ordinate but don't work correctly, just work when add a query builder with order by default.

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

• chore(deps): update dependency @nestjs/swagger to ^7.3.1
• chore(deps): update dependency typeorm to ^0.3.20
• chore(deps): update dependency prettier to ^3.2.5
• chore(deps): update dependency reflect-metadata to ^0.2.2
• chore(deps): update typescript-eslint monorepo to ^7.7.1 (@typescript-eslint/eslint-plugin, @typescript-eslint/parser)
• chore(deps): update dependency eslint to v9
• Click on this checkbox to rebase all open PRs at once

Detected dependencies

docker-compose

docker-compose.yml

github-actions

.github/workflows/main.yml

• actions/checkout v4
• actions/setup-node v4

npm

package.json

• lodash ^4.17.21
• @nestjs/common ^10.3.8
• @nestjs/platform-express ^10.3.8
• @nestjs/testing ^10.3.8
• @types/express ^4.17.21
• @types/jest ^29.5.12
• @types/lodash ^4.17.0
• @types/node ^20.12.7
• @typescript-eslint/eslint-plugin ^7.4.0
• @typescript-eslint/parser ^7.4.0
• dotenv ^16.4.5
• eslint ^8.57.0
• eslint-config-prettier ^9.1.0
• eslint-plugin-prettier ^5.1.3
• fastify ^4.26.2
• jest ^29.7.0
• pg ^8.11.5
• prettier ^3.0.3
• reflect-metadata ^0.1.14
• rxjs ^7.8.1
• sqlite3 ^5.1.7
• ts-jest ^29.1.2
• ts-node ^10.9.2
• typeorm ^0.3.17
• typescript ^5.4.5
• @nestjs/common ^10.3.8
• @nestjs/swagger ^7.3.0
• express ^4.19.2
• fastify ^4.26.2
• typeorm ^0.3.17

---

• Check this box to trigger a request for Renovate to run again on this repository

Filtering with undefined `filterableColumns` throws cryptic error

Using paginate without filterableColumns in the config throws (with in as operator and id as column):

Can't use operator 'in' to find 'id' in undefined

does not get relations

current version does not fetch relations from the database

Repository relations and nested filters

Original: #72 (comment)

And the idea behind the repository relations and nested filters is similar to the current implementation of the filterableColumn.

The developer should be able to indicate the allowlist of routes to populate.

For example
Entities:
User - 1:N - Project - N:1 - City

Paginate config prop:
nestedRoutes: ['projects', 'projects.city']

Query:
?join=projects,projects.city

And over the same allowlist, it would be nice to have some filtering.
For example, from the previous query, if I want to filter all users by project city population.

filterableColumns: {
        projects: {
          city: {
            population: [FilterOperator.GTE, FilterOperator.LTE]
          }
        },
        ...
      },

Query:
?filter.projects.city.population=$gte:100000

Searching by numeric column

Hi, recently i started using this package and came by weird issue. What's happening is when i put column id in searchableColumns and try to search by it like this: {{baseURL}}?search=145&searchBy=id i get error

QueryFailedError: operator does not exist: integer ~~* unknown

I've done some digging and found out that it's because of this line:

qb.orWhere({
    [column]: ILike(`%${query.search}%`),
})

in file paginate.ts line 315.
I do also think it's because of that I'm using Postgres. I think that implicit type cast in WHERE clause is either non existent or i have something badly configured
My "fix" was to replace that with:

qb.orWhere(`${qb.alias}.${column}::VARCHAR ILIKE '%${query.search}%'` )

But it's really not good looking.
Thanks in advance

Customized Links

Normally I don't want the full link like ""http://localhost...?page=4&limit=10&sortBy=id:DESC"
Is there a way to customize the result? We will not use the full route in the frontend.

Nested where does not work

Hi,

I'm having a problem when I want to add conditions using relations fields.

Here is where I call the paginate function:
paginate(query, this.taskMessagesRepository, { relations: ['message'], where: { message: { deleted: false, }, }, sortableColumns: ['id', 'message.deleted'], });

Here are my entites:
export class Message { ... @Index() @Column({ type: 'boolean', default: false }) deleted: boolean; }

export class TaskMessage { ... @ManyToOne(() => Message, { nullable: false }) message: Message; }

The error is "Cannot find alias for relation at message".

Please note that, when I want to display a particular task message with a message.id where statement, the condition pick the messageId of the TaskMessage table.

I don't know if it comes from the library or not but when I copy the conditions and relations using "TypeOrm.find", it works.

(I used the 2.6.0 version of the library). Thanks !

It's possible to use ILIKE?

Has any way to use ILIKE to filter data ? From query builder TYPEORM.

Filtering by dates

I would like to use filtering by dates in my pagination.

Any chance to make the below operators

FilterOperator.LT,
FilterOperator.LTE,
FilterOperator.GTE,
FilterOperator.GT,
FilterOperator.BTW,

working with dates as well?

need help

post here, but I've had enough and I haven't found a solution

example:
tecnicos: [{ id: 1} , { id: 2}]
filter return [{id: 1}], need tecnicos complete

Swagger and PaginateQuery

Hi guyes.

1.There have some qestions about swagger and PaginateQuery.

I have filter function, and i want so that paramater work via POST query.
There if define PaginateQuery with body decorator. I dont see incoming parameteres in swagger UI.

  @Post('filter')
  @ApiOperation({ summary: 'Получить данные фильтрация' })
  filter(@Body() query: PaginateQuery): Promise<Paginated<OrdersEntity>> {
    return this.findAll2(query);
  }

If I create another DTO

import { ApiProperty } from '@nestjs/swagger';

export class OrderCreateDTO {
  @ApiProperty({example: 'Sergey Mikhailov', description: 'ФИО'})  
  name: string;

  @ApiProperty({example: '[email protected]', description: 'E-mail'})
  email: string;
}

And make another type, this works. but is not works out of box.

@Post('filter')
 @ApiOperation({ summary: 'Получить данные фильтрация' })
 filter(@Body() query: PaginateQuery2): Promise<Paginated<OrdersEntity>> {

   var query2:PaginateQuery;
   query2=<PaginateQuery>query;
   return this.findAll2(query2);
 }

What Ure recomndations, and to make work via post ???

Because if there will be about 10 fields filtration, URL will be very ugly !!!
http://localhost:3000/cats?limit=5&page=2&sortBy=color:DESC&search=i&filter.age=$gte:3

2.Questions is, i need swagger description, because I generate UI Angular code via ng-openapi-gen.
And i need described swagger definition!

Sort nested fields for query builders

Currently, sortable columns inside the config must be fields of the given entity. However, If the field is a joined entity, you cannot select a nested field for sorting.

Example:

TaskEntity {                           UserEntity {  
   id: number,                             id: number,                
   title: string,                          firstName: string,
   createdBy: User                         lastName: string
}                                      }

Query:
await paginate<TaskEntity>(query, queryBuilder, { sortableColumns: ['title', 'createdBy.firstName', 'createdBy.lastName']});

I am not allowed to select createdBy.firstName or createdBy.lastName as a sortable column because there are no direct fields of TaskEnitty. Do you have any solutions?

AND condition for filter on a single column

I wanted to match mutliple genres of a game with an and condition, but only the last condition gets applied. How to achieve something like this?

&filter.genres.name=$eq:Indie&filter.genres.name=$eq:RPG

&filter.genres.name=$eq:Indie,RPG -> wont work

&filter.genres.name=$in:Indie,RPG -> is OR condition

Limit search scope with "searchBy" query param

Original: #72 (comment)

And one more thing.
It would be nice to provide an extra query key searchBy to indicate on which column we should search.
What do you think?

Yup. Seems handy. By default search through all columns provided by searchableColumns; limit search query further by columns indicated in searchBy.

JSONB support

How to use filterableColumns with Postgres JSONB ?

const assigned_bids = await paginate<Bid>(request, qb, {
        relations: ['product', 'statuses', 'basisBuyer'],
        sortableColumns: sortBy.map((e) => e[0]) as PaginateConfig<Bid>['sortableColumns'],
        searchableColumns: (request.searchBy as PaginateConfig<Bid>['searchableColumns']) || [],
        defaultLimit: 4,
        maxLimit: 500,
        where: { user: { id: In(assignedIds) } },
        filterableColumns: {
          year: [FilterOperator.EQ, FilterOperator.NOT, FilterOperator.IN],

          mass: [FilterOperator.EQ, FilterOperator.BTW, FilterOperator.GTE, FilterOperator.NOT, FilterOperator.IN],
          'basisBuyer.id': [FilterOperator.EQ],
          'product.nomenclatureGroupId': [FilterOperator.EQ],

          // deliveryDate is JSONB field in PostgreSQL. ex:
          /*
          {
            "to": "2022-06-26",
            "from": "2022-06-20"
          }
          */
          deliveryDate: [FilterOperator.EQ, FilterOperator.BTW, FilterOperator.IN]

       
        }

Thanks

disable pagination

Looking at the name of the project probably out of the scope :D

but is there a way to disable pagination (i just need to reuse the filters and maybe sorting and other applicable logic)?

Need to include in query sum of amounts of relations

Hello, we are using this fantastic lib in our project, but faced a problem, and would like to get some advice on this and see if it even possible to achieve. We have 2 entities:

class Charge {
    amount: number
}

class Transaction {
    charges: Charge[]
}

We need to add filter that filters by the sum of charges amount. Something like this:

const queryBuilder = this.transactionRepository
      .createQueryBuilder('transaction')
      .leftJoinAndSelect('transaction.charges', 'charges')
      .addSelect(`SUM(charges.amount)`, totalAmount)
      .where('totalAmount = :totalAmount', { totalAmount})


return paginate(query, queryBuilder, {...})

As far as I know after some research it's possible to do with getRawMany, but as I know paginate is using getManyAndCount.

Are there any other options how can we achieve this and keep using paginate lib?
Appreciate for any feedback.

Does it support swagger?

This is not working with nestjs/swagger yet...

Filter not working on boolean

export const CATEGORY_AVAILABLE_FILTERS = {
isActive: [FilterOperator.EQ],
};

//postman
http://127.0.0.1:3000/api/v1/categories?search=rice&filter.isActice=true

//response getting
data: [
Category {
id: 64,
name: 'Rice 1',
description: null,
isActive: true,
createdAt: 2022-09-23T09:55:59.141Z
},
Category {
id: 63,
name: 'rice',
description: 'Best rice in the town........',
isActive: false,
createdAt: 2022-09-23T07:37:44.347Z
}
],
meta: {
itemsPerPage: 20,
totalItems: 2,
currentPage: 1,
totalPages: 1,
sortBy: [ [Array] ],
search: 'rice',
searchBy: [ 'name' ],
filter: { isActice: 'true' }
},
links: {
first: undefined,
previous: undefined,
current: 'http://127.0.0.1:3000/api/v1/categories?page=1&limit=20&sortBy=id:DESC&search=rice&filter.isActice=true',
next: undefined,
last: undefined
}
}

searchBy throwing error in case of upper case fields for postgres

Hey Philipp!
We need to do a hotfix regarding the latest release of the package with 4.6.2.
The change introduced with #442 is now having problems with upper case variable names.

Currently already working on a fix.

Here the error...

WHERE ("user"."deleted" = $1) AND (user.firstName::text ILIKE '%admin%' OR user.lastName::text ILIKE '%admin%' OR user.email::text ILIKE '%admin%' OR user.role::text ILIKE '%admin%')) "distinctAlias" ORDER BY "distinctAlias"."user_lastName" ASC, "user_id" ASC LIMIT 20","parameters":[false],"driverError":{"length":92,"name":"error","severity":"ERROR","code":"42601","position":"6379","file":"scan.l","line":"1180","routine":"scanner_yyerror"},"length":92,"severity":"ERROR","code":"42601","position":"6379","file":"scan.l","line":"1180","routine":"scanner_yyerror"}

How can i use $btw param with Date Format?

How can i use $btw param with Date Format?
i want to search with Date type params

Type of `where` should allow array of objects as well

According to TypeORM docs, the where find option should also support an array of options to apply an OR operator:

userRepository.find({
    where: [
        { firstName: "Timber", lastName: "Saw" },
        { firstName: "Stan", lastName: "Lee" },
    ],
});

-> SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')

Providining such array however results in the following TS error for me:

Type '({ property1: value1; } | { property2: value2; })[]' has no properties in common with type 'FindConditions<Entity>'.

Is this an error on my side or does the library not support this feature?

Extreme ineficient queries when using relations

Hello, I'm using nestjs-paginate for fetch a entity with it relations.
But seems that the query is extremely slow because the library make 3 queries joining all related tables without the need to do this.
Example:
Config:

paginate<Vehicle>(query, this.vehicleRepository, {
      where: {
        clientId,
        deleted: false,
      },
      sortableColumns: ['updatedAt', 'reference', 'value'],
      searchableColumns: ['reference', 'brand.name', 'model.name', 'trim.name'],
      defaultLimit: 10,
      maxLimit: 50,
      filterableColumns: { reference: [FilterOperator.EQ], type: [FilterOperator.EQ] },
      relations: [
        'pictures',
        'accessories',
        'model',
        'brand',
        'trim',
        'publication',
      ],
    });

The 3 queries that it runs:

# [2022-09-21T16:14:37.144Z][SLOW QUERY: 2946 ms]:
SELECT DISTINCT `distinctAlias`.`e_id` AS `ids_e_id`, `distinctAlias`.`e_data_alteracao`
FROM (SELECT `e`.`id`                                              AS `e_id`,
             `e`.`titulo`                                          AS `e_titulo`,
             `e`.`descricao`                                       AS `e_descricao`,
             `e`.`referencia`                                      AS `e_referencia`,
             `e`.`tipo_veiculo`                                    AS `e_tipo_veiculo`,
             `e`.`placa`                                           AS `e_placa`,
             `e`.`numero_documento`                                AS `e_numero_documento`,
             `e`.`quilometragem`                                   AS `e_quilometragem`,
             `e`.`zero_km`                                         AS `e_zero_km`,
             `e`.`qtd_portas`                                      AS `e_qtd_portas`,
             `e`.`ano_fabricacao`                                  AS `e_ano_fabricacao`,
             `e`.`id_cor`                                          AS `e_id_cor`,
             `e`.`id_versao`                                       AS `e_id_versao`,
             `e`.`id_modelo`                                       AS `e_id_modelo`,
             `e`.`id_marca`                                        AS `e_id_marca`,
             `e`.`ano_modelo`                                      AS `e_ano_modelo`,
             `e`.`id_cambio`                                       AS `e_id_cambio`,
             `e`.`id_combustivel`                                  AS `e_id_combustivel`,
             `e`.`data_alteracao`                                  AS `e_data_alteracao`,
             `e`.`valor`                                           AS `e_valor`,
             `e`.`valor_parcelas`                                  AS `e_valor_parcelas`,
             `e`.`qtd_parcelas`                                    AS `e_qtd_parcelas`,
             `e`.`valor_entrada`                                   AS `e_valor_entrada`,
             `e`.`id_cliente`                                      AS `e_id_cliente`,
             `e`.`ativo`                                           AS `e_ativo`,
             `e_pictures`.`id`                                     AS `e_pictures_id`,
             `e_pictures`.`id_veiculo`                             AS `e_pictures_id_veiculo`,
             `e_pictures`.`foto`                                   AS `e_pictures_foto`,
             `e_pictures`.`url_original`                           AS `e_pictures_url_original`,
             `e_pictures`.`data_cadastro`                          AS `e_pictures_data_cadastro`,
             `e_pictures`.`ordem`                                  AS `e_pictures_ordem`,
             `e_pictures`.`hash`                                   AS `e_pictures_hash`,
             `e_accessories`.`id`                                  AS `e_accessories_id`,
             `e_accessories`.`nome`                                AS `e_accessories_nome`,
             `e_model`.`id`                                        AS `e_model_id`,
             `e_model`.`nome`                                      AS `e_model_nome`,
             `e_model`.`id_marca`                                  AS `e_model_id_marca`,
             `e_brand`.`id`                                        AS `e_brand_id`,
             `e_brand`.`nome`                                      AS `e_brand_nome`,
             `e_brand`.`tipo_veiculo`                              AS `e_brand_tipo_veiculo`,
             `e_trim`.`id`                                         AS `e_trim_id`,
             `e_trim`.`nome`                                       AS `e_trim_nome`,
             `e_trim`.`id_modelo`                                  AS `e_trim_id_modelo`,
             `e_publication`.`id_veiculo`                          AS `e_publication_id_veiculo`,
             `e_publication`.`id_veiculo_plano_assinatura_cliente` AS `e_publication_id_veiculo_plano_assinatura_cliente`,
             `e_publication`.`destaque`                            AS `e_publication_destaque`
      FROM `tb_veiculo` `e`
               LEFT JOIN `tb_veiculo_foto` `e_pictures` ON `e_pictures`.`id_veiculo` = `e`.`id`
               LEFT JOIN `tb_veiculo_opcional_veiculo` `e_e_accessories` ON `e_e_accessories`.`id_veiculo` = `e`.`id`
               LEFT JOIN `tb_veiculo_opcional` `e_accessories`
                         ON `e_accessories`.`id` = `e_e_accessories`.`id_veiculo_opcional`
               LEFT JOIN `tb_veiculo_marca_modelo` `e_model` ON `e_model`.`id` = `e`.`id_modelo`
               LEFT JOIN `tb_veiculo_marca` `e_brand` ON `e_brand`.`id` = `e`.`id_marca`
               LEFT JOIN `tb_veiculo_marca_modelo_versao` `e_trim` ON `e_trim`.`id` = `e`.`id_versao`
               LEFT JOIN `tb_veiculo_plano_assinatura_cliente_veiculo` `e_publication`
                         ON `e_publication`.`id_veiculo` = `e`.`id`
      WHERE ((`e`.`id_cliente` = ? AND `e`.`deleted` = ?))) `distinctAlias`
ORDER BY `distinctAlias`.`e_data_alteracao` ASC, `e_id` ASC
LIMIT 10
-- PARAMETERS: [77972,false]
# [2022-09-21T16:14:41.147Z][SLOW QUERY: 3998 ms]:
SELECT `e`.`id`                                              AS `e_id`,
       `e`.`titulo`                                          AS `e_titulo`,
       `e`.`descricao`                                       AS `e_descricao`,
       `e`.`referencia`                                      AS `e_referencia`,
       `e`.`tipo_veiculo`                                    AS `e_tipo_veiculo`,
       `e`.`placa`                                           AS `e_placa`,
       `e`.`numero_documento`                                AS `e_numero_documento`,
       `e`.`quilometragem`                                   AS `e_quilometragem`,
       `e`.`zero_km`                                         AS `e_zero_km`,
       `e`.`qtd_portas`                                      AS `e_qtd_portas`,
       `e`.`ano_fabricacao`                                  AS `e_ano_fabricacao`,
       `e`.`id_cor`                                          AS `e_id_cor`,
       `e`.`id_versao`                                       AS `e_id_versao`,
       `e`.`id_modelo`                                       AS `e_id_modelo`,
       `e`.`id_marca`                                        AS `e_id_marca`,
       `e`.`ano_modelo`                                      AS `e_ano_modelo`,
       `e`.`id_cambio`                                       AS `e_id_cambio`,
       `e`.`id_combustivel`                                  AS `e_id_combustivel`,
       `e`.`data_alteracao`                                  AS `e_data_alteracao`,
       `e`.`valor`                                           AS `e_valor`,
       `e`.`valor_parcelas`                                  AS `e_valor_parcelas`,
       `e`.`qtd_parcelas`                                    AS `e_qtd_parcelas`,
       `e`.`valor_entrada`                                   AS `e_valor_entrada`,
       `e`.`id_cliente`                                      AS `e_id_cliente`,
       `e`.`ativo`                                           AS `e_ativo`,
       `e_pictures`.`id`                                     AS `e_pictures_id`,
       `e_pictures`.`id_veiculo`                             AS `e_pictures_id_veiculo`,
       `e_pictures`.`foto`                                   AS `e_pictures_foto`,
       `e_pictures`.`url_original`                           AS `e_pictures_url_original`,
       `e_pictures`.`data_cadastro`                          AS `e_pictures_data_cadastro`,
       `e_pictures`.`ordem`                                  AS `e_pictures_ordem`,
       `e_pictures`.`hash`                                   AS `e_pictures_hash`,
       `e_accessories`.`id`                                  AS `e_accessories_id`,
       `e_accessories`.`nome`                                AS `e_accessories_nome`,
       `e_model`.`id`                                        AS `e_model_id`,
       `e_model`.`nome`                                      AS `e_model_nome`,
       `e_model`.`id_marca`                                  AS `e_model_id_marca`,
       `e_brand`.`id`                                        AS `e_brand_id`,
       `e_brand`.`nome`                                      AS `e_brand_nome`,
       `e_brand`.`tipo_veiculo`                              AS `e_brand_tipo_veiculo`,
       `e_trim`.`id`                                         AS `e_trim_id`,
       `e_trim`.`nome`                                       AS `e_trim_nome`,
       `e_trim`.`id_modelo`                                  AS `e_trim_id_modelo`,
       `e_publication`.`id_veiculo`                          AS `e_publication_id_veiculo`,
       `e_publication`.`id_veiculo_plano_assinatura_cliente` AS `e_publication_id_veiculo_plano_assinatura_cliente`,
       `e_publication`.`destaque`                            AS `e_publication_destaque`
FROM `tb_veiculo` `e`
         LEFT JOIN `tb_veiculo_foto` `e_pictures` ON `e_pictures`.`id_veiculo` = `e`.`id`
         LEFT JOIN `tb_veiculo_opcional_veiculo` `e_e_accessories` ON `e_e_accessories`.`id_veiculo` = `e`.`id`
         LEFT JOIN `tb_veiculo_opcional` `e_accessories`
                   ON `e_accessories`.`id` = `e_e_accessories`.`id_veiculo_opcional`
         LEFT JOIN `tb_veiculo_marca_modelo` `e_model` ON `e_model`.`id` = `e`.`id_modelo`
         LEFT JOIN `tb_veiculo_marca` `e_brand` ON `e_brand`.`id` = `e`.`id_marca`
         LEFT JOIN `tb_veiculo_marca_modelo_versao` `e_trim` ON `e_trim`.`id` = `e`.`id_versao`
         LEFT JOIN `tb_veiculo_plano_assinatura_cliente_veiculo` `e_publication`
                   ON `e_publication`.`id_veiculo` = `e`.`id`
WHERE (((`e`.`id_cliente` = ? AND `e`.`deleted` = ?)))
  AND (`e`.`id` IN (1677173, 1677365, 1677421, 1677438, 1677461, 1677629, 1677634, 1676263, 1676269, 1676275))
ORDER BY `e`.`data_alteracao` ASC; -- PARAMETERS: [77972,false]
#     [2022-09-21T16:14:41.358Z][SLOW QUERY: 174 ms]:
SELECT COUNT(DISTINCT `e`.`id`) AS `cnt`
FROM `tb_veiculo` `e`
         LEFT JOIN `tb_veiculo_foto` `e_pictures` ON `e_pictures`.`id_veiculo` = `e`.`id`
         LEFT JOIN `tb_veiculo_opcional_veiculo` `e_e_accessories` ON `e_e_accessories`.`id_veiculo` = `e`.`id`
         LEFT JOIN `tb_veiculo_opcional` `e_accessories`
                   ON `e_accessories`.`id` = `e_e_accessories`.`id_veiculo_opcional`
         LEFT JOIN `tb_veiculo_marca_modelo` `e_model` ON `e_model`.`id` = `e`.`id_modelo`
         LEFT JOIN `tb_veiculo_marca` `e_brand` ON `e_brand`.`id` = `e`.`id_marca`
         LEFT JOIN `tb_veiculo_marca_modelo_versao` `e_trim` ON `e_trim`.`id` = `e`.`id_versao`
         LEFT JOIN `tb_veiculo_plano_assinatura_cliente_veiculo` `e_publication`
                   ON `e_publication`.`id_veiculo` = `e`.`id`
WHERE ((`e`.`id_cliente` = ? AND `e`.`deleted` = ?)) -- PARAMETERS: [77972,false]

I think that query 2 should not exist or if it not exits the query shouldn't include the left joins.
is there any way to optimize library queries?

Swagger integration

It would be nice to have swagger documentation support for the parameters. Currently it's not possible to change the limit, page or filter on the docs page.

Support for typeorm 0.3.0

Currently typeorm peer dependency is set to <0.3.0.
As nestjs-typeorm v8.1.0 uses typeorm v0.3+ instead of v0.2+ it would be great to also upgrade here in order to fully migrate to 0.3+

Thanks, Jakob

Option to isolate individual config options to generate more intuitive query

I would like to have an option to isolate individual config options to make query more intuitive.

Example

For the library implementation in SomeService:

paginate(query, this.someRepository, {
  sortableColumns: [...],
  searchableColumns: [...],
  filterableColumns: {...},
  where: [{<property1: value1>}, {<property2: value2>}]
}

and the following request:

path/to/endpoint?filter.<property3>=$eq:<value3>

You will get records that fit this query:
WHERE (property1 = value1) OR (property2 = value2) AND (property3 = value3)
which thanks to the operator precedence is the same as:
WHERE (property1 = value1) OR ((property2 = value2) AND (property3 = value3))

However, at least for me personally, it would make more sense to me to generate the following query:
WHERE ((property1 = value1) OR (property2 = value2)) AND (property3 = value3)

Discussion

The actual query is a condition of andWhere, which is used in the library for individual config options. The (non-intuitive?) behavior of andWhere has been previously discussed in TypeORM repository (typeorm/typeorm#1568, typeorm/typeorm#6170, etc.) and closed as not a bug, working as intended.

In the linked issues, there have been tips to use TypeORM's Brackets class to achieve the desired functionality, as well as some proposed features (link).

If this is not considered a bug, as in following TypeORM rules, would it be possible to implement some option for this library to easily enforce the parentheses (and therefore the desired query)? Or would it make sense to enforce isolation for the where config option by default?

Not able to search in relations of relations (Nested Relations throw missing FROM-clause entry)

I wanted to implement a search using a property from a relation of a relation from my Entity.

json searchableColumns: [ "title", "games.genres.name" ],

But i get this:

error:   ┏ missing FROM-clause entry for table "genres"
error:   ┃ [ 1] QueryFailedError: missing FROM-clause entry for table "genres"
error:   ┃ [ 2]     at PostgresQueryRunner.query (/app/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
error:   ┃ [ 3]     at runMicrotasks (<anonymous>)
error:   ┃ [ 4]     at processTicksAndRejections (node:internal/process/task_queues:96:5)
error:   ┃ [ 5]     at async SelectQueryBuilder.loadRawResults (/app/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2056:25)
error:   ┃ [ 6]     at async SelectQueryBuilder.getRawMany (/app/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:607:29)
error:   ┃ [ 7]     at async SelectQueryBuilder.executeEntitiesAndRawResults (/app/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:1860:26)
error:   ┃ [ 8]     at async SelectQueryBuilder.getManyAndCount (/app/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:771:36)
error:   ┃ [ 9]     at async paginate (/app/node_modules/.pnpm/[email protected]_5lrjep46ocnlg5z2ezquymw4bm/node_modules/nestjs-paginate/lib/paginate.js:283:27)
error:   ┃ [10]     at async TagsController.get (/app/dist/controllers/tags.controller.js:32:34)
error:   ┃ [11]     at async /app/node_modules/.pnpm/@[email protected]_jfowmhmgk7kwjudosrpv7e7liy/node_modules/@nestjs/core/router/router-execution-context.js:46:28
error:   ┗ [12] { context: 'ExceptionsHandler' }

Aren't nested relations supported in the search?

Error for columns defined as enum when search

Hello !
I get an error when trying to search for a property defined as enum.
I think this is the reason why: https://stackoverflow.com/questions/35072844/using-ilike-on-an-enum-in-sql

here the created SQL-Statement:
WHERE ... OR "user"."role" ILIKE $3 ...

"parameters": [
	"%Admin%",
	"%Admin%",
	"%Admin%",
	"%Admin%"
],

I received following error...
"driverError": {
"length": 214,
"name": "error",
"severity": "ERROR",
"code": "42883",
"hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
"position": "3319",
"file": "parse_oper.c",
"line": "731",
"routine": "op_error"
},

Hope you can help here.
BR Jakob

Links Undefined

Hi, in my links section it contains undefined which should be app url, is there any option or maybe I miss something?
I've set APP_URL in .env but it's still undefined

This is my service

async getChatNotification(query: PaginateQuery): Promise<Paginated<ChatNotificationEntity>> {
  return paginate(query, this.chatNotification, {
    where: { deleted_at: null },
    sortableColumns: ['id', 'title', 'type', 'content', 'read_at'],
    searchableColumns: ['id', 'title', 'type', 'shop_id', 'content', 'read_at'],
    defaultSortBy: [['id', 'DESC']],
  })
}

How add repository relations?

Example:
this.repository.findAll({
relations: ['user'],
});

`defaultFilter` proposal

Often in medium sized projects, when you have multiple users/tenants and they query a /resources endpoint, they don't want to see all the resources on the application, but their resources. This would mean that when I query /resources I'd like a default filter of /resources?filter.user.id=$eq:authUserId. It occurs often enough that I'd like to not have to specify it neither in the frontend query nor the backend controller logic.

I propose some additions to PaginateConfig that I can PR when we agree on the proposal:

• defaultFilter can be set to valid filters (see below).
• When defaultFilter is set, and there are no queried filters, defaultFilter is used. When queried filters are set, the defaultFilter is not applied.
• [bonus] defaultFilterMode:
  • FilterMode.defaultRemoved: default dissapears when filters are set
  • FilterMode.defaultCombined: filters merged over default
  • FilterMode.defaultAlwaysOn: default filter merged with priority over query filters

Valid filters would be using the data structures nestjs-paginate uses to represent filters as produced by the Paginate param decorator. They currently don't seem specified, but from the source code it doesn't seem too complicated to write a spec and docs on them:

nestjs-paginate/src/decorator.ts

Lines 53 to 61 in 8465699

	const filter = mapKeys(
	pickBy(
	query,
	(param, name) =>
	name.includes('filter.') &&
	(isString(param) || (Array.isArray(param) && (param as any[]).every((p) => isString(p))))
	) as Dictionary<string | string[]>,
	(_param, name) => name.replace('filter.', '')
	)

Help wanted on new feature (deeply nested relations)

Hello! I'm making a modification on the library so that the relations columns will accept nested relations. But I'm struggling to adapt the type RelationColumn to work with deeply nested relations.
e.g:

class Owner {
  name!: string;
}
class House {
   owner!: Owner;
}
class Cat {
  house!: House;
}


const relations: RelationColumn<Cat>[] = ['house', 'house.owner']
//                                                  ^not works today

Can someone help me to adjust the type RelationColumn so i can open a PR?
Ts playground for reference:

https://www.typescriptlang.org/play?#code/C4TwDgpgBAUg9gSwHYB4DSAaKAFAfFAXijSggA9gIkATAZyluACdkBzKAfigAptSKqdBszacoAAwAkAbzQBfGQHJF-SjXp8uyqAC4oigHSKF07HPG6oSCADcITAJSXrdpgCg3oSDia3CUAG0XeywABiwARiwAJiwAZiwAFiwAViwANiwAdiwADiwATkjwqAMy0ICAXUqPAHpaqAAVAHkAEWa9MABXYHpaOABbaABjQaGkXqgAC3sILGQoYCmEekomJABDJhAoOAAzKGo4QbdyMDgmYEXwaABhOAAbLoHURqxW1UF6JGeAI3t-NF8EQAq1Kp91IFgkwalA4WJoW54ZZGhChAAlCCjJjUFCMFhIVhYDZIEC4JHwrjSCnI5EBEgLADWEBA+yalQAtBw9CRyGohPi2DTaSKuI16eC+V8oK0NpRhSLFWIpLJzAqlbS9OK0JKBJCAIJMJgbEAoZB7AEAVXJGo1XBV8gsAB9YIhUJgoPcni8UJasNhfDZQZVcDbbYq9A7zFAXfBkOgsF7nq8Jf7A8HQ+qlXpESK5AFmayDo1YfC9MoPGcLlcvNBMQ85Qg4Egkz7GsCoABRCjG4bAFDC1uvXAYYXUpX0qALIcoduVHlo+gq80A9FyAwyQWE6NcdHOWz2YX5mdztzk05kc6Xa7eZo4+yzjsBE8j-T6gDKtxULsUrU7n8UGoq2vWsoHfasACFTXbfw72oB92yqDw3GGBtaHoZoAHdrCYKBxysDYhgAQj0LdWAAbjcOQULQ+gAAk4C6WhoHw3ZsPsEioCwnDKOo1CNnQz05TwikpkY5jOIYpiIF45DRiQRgoF8BtgCbBS9HrRtmxnW45VwKp-ACRQxOkxQsGM8SIAMOB2KYQC3HqcMnOc5yAD0kDgK5MIuRlVjgagTSAA

Sorting by NULL LAST

Hello,

Correct me if I am wrong, but there is currently no way to define in sortableColumns config, that null records should be sorting as last and not first.

If anyone has any workaround, feel free to correct me <3

Thanks!

where clause in query builder being overwritten

paginate<Project>(
  query,
  this.projectRepository
    .createQueryBuilder('project')
    .leftJoinAndSelect('project.user', 'user')
    .leftJoinAndSelect('project.subusers', 'subuser')
    .andWhere('subuser.user.id = 2'),
  {
    sortableColumns: ['id', 'name', 'createdAt'],
    searchableColumns: ['name'],
    defaultSortBy: [['id', 'DESC']],
  },
);

Since I need to use a where clause on a joined table I have to add it through the query builder but it seems to be getting overwritten in the paginate function.

Roadmap ?

Hi, this library looks awesome!

I like that is so simple. without a ton of decorators and easy to implement.
I've been using nestjsx/crud and it's overwhelmed.

Are you planning to enhance it with new features?
I would like to contribute some ideas and maybe even go ahead with them.

What do you think about:

1. Serialize the data array
2. Nested filters
3. Add relations from the query

Unable to filter on values that contain a colon.

I have some table IDs that contain colons as they are made up of two things concatenated together. I was trying to filter on this value and found the results just returned everything. I took a look and found it's likely to do with the way the filter value is split up.

nestjs-paginate/src/paginate.ts

Line 178 in 85f6019

const tokens = raw.split(':')

Is there any way to make this work or should I be ditching the colon and using another separator whilst I still have the chance?

not work with nestjs crud

How can I combine FilterOperators? And how can I choose AND/OR Filters relation?

I need to combine FiltersOperators, such as $gte and $lte ($between), $not and $in... If I insert the filters as query parameters, only the last one related to a column is considered.

I would also like to understand how to choose the type of relationship between the filters (AND,OR)

Feature Request: let choose the response body of Paginated class

Hi,

The paginated response includes the following structure: data, meta and links.
There are some cases I need to change the name of the properties, can you please add the ability to do this?

Thanks in advanced!

Sorting alphabetical insensitive [Not Bug]

I know it should work like that, but is there away to customise the default behaviour

instead of this

{
  "data": [{ "name": "Apple"}, {"name": "Orange"}, {"name": "banana"}]
}

I want this

{
  "data": [{ "name": "Apple"}, {"name": "banana"}, {"name": "Orange"}]
}

What should I do if I want to use the field in the join table as a search (or filter) condition?

Hello

While implementing the search (filter) function, I have a question because I am curious.

What should I do if I want to implement the following examples?
(1:1 or 1:N relationship table)

Should we wait until the issue(#81 ) is resolved?

uuid column throws when defined as search field

Hi.

I have an issue when I add a PostgreSQL uuid or jsonb type field to the searchableColumns.
I get such errors:

QueryFailedError: operator does not exist: jsonb ~~* unknown
    at PostgresQueryRunner.query (PostgresQueryRunner.ts:299:19)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at SelectQueryBuilder.loadRawResults (SelectQueryBuilder.ts:3601:25)
    at SelectQueryBuilder.getRawMany (SelectQueryBuilder.ts:1573:29)
    at SelectQueryBuilder.executeEntitiesAndRawResults (SelectQueryBuilder.ts:3295:26)
    at SelectQueryBuilder.getManyAndCount (SelectQueryBuilder.ts:1776:36)
    at paginate (node_modules/nestjs-paginate/src/paginate.ts:316:28)
    at AuditLogService.get (audit-log.service.ts:600:22)
    at node_modules/@nestjs/core/router/router-execution-context.js:46:28
    at node_modules/@nestjs/core/router/router-proxy.js:9:17

I tried to create a querybuilder, pass it to paginate (which by default works) and tried to remove my jsonb field and add it manually like this:

let queryBuilder = this.auditLogRepo.createQueryBuilder();
if (query.search) {
    queryBuilder = queryBuilder.orWhere('payload::text ILIKE :search', {
        search: `%${query.search}%`,
    });
}

This however does not work. It only works if I delete query.search, but obviously then it will not search for the other searchableColumns.

Is there any solution to this?

Include and filter by properties in nested relations

Hi,

I'm trying to get relations to work with nested relations. Let's say I have entities A, B, and C.
A has a Many-to-One to B, and B has a One-to-One to C.
If I am using the pagination on A, but trying to filter by values on C, it looks like I can't.
relations option seems to only accept "surface" relations, and I've been trying to build a custom query builder but even if I can manage to include these relations, filtering seems to still be broken.

Am I missing something?
Thanks.

How can I do paging using QueryBuilder?

In the examples it appears using repositories, but if for example I do my query with queryBuilder I have printed Raw "getRawMany();"

How can I implement it? Could you give some examples of it?

How to use nestjs-paginate with limiting the query fields results?

is there a way to defind /cats?fields=field1,field2 etc? ats the movement even using some custom solution?

E.g. provide also somehow FindOneOptions options when calling

paginate(query, this.repository, config);

Does not search for deleted

HELPPP

Hello I have several tables in my system with deletedAt, but the typeorm does not search if it has a defined date, it will only bring the data if it is null. But there is a flag { withDeleted: true } which will return deleted and non-deleted records, how do I do it by nestjs-paginate

Filter does not work with QueryBuilder

If I use a querybuilder, the filters doesnt work. Is this not supported by default?

Serialize the data array

Original: #72 (comment)

With serialization, I mean filtering and transforming the response data. Using class-transformer, for example.

For now, in my code, I have implemented an interceptor (also could be a class or helper function) to catch the Paginated and call the plainToClass() for data param. Here I can provide a small example for the better understanding:

// controller

const paginatedResults = await paginate(...) // Item[]
return {
    ...paginatedResults,
    data: plainToClass(ItemResponseDto, paginatedResults.data)
}

// ItemResponseDto with class-transformer

@Exclude()
export class ItemResponseDto {
  @Expose()
  id: string;
...
}

Having written this, I think this logic should be the responsibility of this module. So, nevermind 😅