Using Limits lets API return only a single result in Data Array

[Alfagun74]

After updating from version 4.12.1 to 8.0.0 I get again an error like at #470. My api returns only 1 entry in the data array result although I should see 50 (of 400) when i use the limit option.

GET /api/v1/games?limit=50

{
"data": [
  { 1 REDACTED Entry}
],
"meta": {
        "itemsPerPage": 50,
        "totalItems": 399,
        "currentPage": 1,
        "totalPages": 8,
        "sortBy": [
            [
                "id",
                "ASC"
            ]
        ]
    },
}

GET /api/v1/games

{
"data": [
  { 399 REDACTED Entries},...
],
"meta": {
        "itemsPerPage": 9007199254740991, // I set maxsafe integer as default limit to disable pagination by default
        "totalItems": 399,
        "currentPage": 1,
        "totalPages": 1,
        "sortBy": [
            [
                "id",
                "ASC"
            ]
        ]
    },
}

[ppetzold]

dropped take/skip support as it's super buggy with most of the feature.

please submit PR with test case which reproduces your issue.

[Alfagun74]

@ppetzold i am not aware of using take/skip how would i find out if i did?

[ppetzold]

when you first reported #470 we just introduced the change from take/skip to limit/offset.

take/skip is related to many known typeorm issues and produces quite unperformant double distinct queries. it also was the cause for the majority of the reported bugs on this package.

back then I didn't realize this and approved the change back from limit/offset to take/skip again - which apparantly solved your issue.

due to typeorm bugs, it's impossible to get all test cases pass with take/skip. so I dropped it with c42fde6

if you can submit a test case where limit/offset fails, I am happy to look into the cause.

[ppetzold]

See

typeorm/typeorm#4742
typeorm/typeorm#5670

[ppetzold]

Ah btw. just noticed you extended your first comment.

We have an official way of disabling pagination. Try maxLimit=0 and in query ?limit=0

Does this solve?

FYI breaking change coming here some time soon:
#393 (comment)

[Alfagun74]

when you first reported #470 we just introduced the change from take/skip to limit/offset.

take/skip is related to many known typeorm issues and produces quite unperformant double distinct queries. it also was the cause for the majority of the reported bugs on this package.

back then I didn't realize this and approved the change back from limit/offset to take/skip again - which apparantly solved your issue.

due to typeorm bugs, it's impossible to get all test cases pass with take/skip. so I dropped it with c42fde6

if you can submit a test case where limit/offset fails, I am happy to look into the cause.

Hi Philipp,

Bummer.... limit/offset is broken for me it seems.

But i cant seem to reproduce my issue in your test suite in a rush.

I also noticed that i only have the broken limits when i use a real posgres database instead of sqlite3,so i need further setup using your DB environment variable and a local postgres container to test my problem for real. Which doesn't work for me right now, because i have trouble getting docker running on this crappy pc im on currently.

So far i already spotted the absence of a "should limit cats" test like this though:

it('should limit cats', async () => {
        const config: PaginateConfig<CatEntity> = {
            sortableColumns: ['id'],
            defaultLimit: Number.MAX_SAFE_INTEGER,
        }
        const query: PaginateQuery = {
            path: '',
            limit: 2,
        }
        console.log((await catRepo.find()).length)

        const result = await paginate<CatEntity>(query, catRepo, config)

        expect(result.data).toStrictEqual(cats.slice(0, 2))
    })

It works fine with sqlite. Have you got a Postgres running by any chance and could run this using it?

[Alfagun74]

Ah btw. just noticed you extended your first comment.

We have an official way of disabling pagination. Try maxLimit=0 and in query ?limit=0

Does this solve?

FYI breaking change coming here some time soon: #393 (comment)

interesting.. let me try real quick

[ppetzold]

b55898d

passes for me locally on both. CI also runs against postgres and sqlite.

https://github.com/ppetzold/nestjs-paginate/actions/runs/4494473656

[Alfagun74]

okay so:
maxLimit 0 as you suggested combined with query limit=0 returns all 399 entries for me.
but still:
maxLimit 0 + query limit=5 only returns 1 entry for me, (but the metadata says there should be 5 items per page)

[ppetzold]

what typeorm version are you using? do you pass query builder or repo?

[Alfagun74]

i am using

"typeorm": "^0.3.12",
"typeorm-naming-strategies": "^4.1.0",

And the Repo variant using Repository<Game>

Could it be the naming strategy?

[ppetzold]

that's very likely. if that's the case tho, then issue is not with this package but with typeorm-naming-strategies and using typeorm query builder with limit/offset. possible some internal mapping issue (if you don't see anything wrong with the generated sql query)

[Alfagun74]

i found something interesting

I logged out the queries in my servers postgres and ran the query that nestjs-paginate/typeorm ran on my database by hand... (its a videogame database)

SELECT "__root"."id" AS "__root_id",
       "__root"."rawg_id" AS "__root_rawg_id",
       "__root"."title" AS "__root_title",
       "__root"."rawg_title" AS "__root_rawg_title",
       "__root"."version" AS "__root_version",
       "__root"."release_date" AS "__root_release_date",
       "__root"."rawg_release_date" AS "__root_rawg_release_date",
       "__root"."creation_date" AS "__root_creation_date",
       "__root"."cache_date" AS "__root_cache_date",
       "__root"."file_path" AS "__root_file_path",
       "__root"."size" AS "__root_size",
       "__root"."description" AS "__root_description",
       "__root"."website_url" AS "__root_website_url",
       "__root"."metacritic_rating" AS "__root_metacritic_rating",
       "__root"."average_playtime" AS "__root_average_playtime",
       "__root"."deleted_date" AS "__root_deleted_date",
       "__root"."early_access" AS "__root_early_access",
       "__root"."box_image_id" AS "__root_box_image_id",
       "__root"."background_image_id" AS "__root_background_image_id",
       "__root_developers"."id" AS "__root_developers_id",
       "__root_developers"."rawg_id" AS "__root_developers_rawg_id",
       "__root_developers"."name" AS "__root_developers_name",
       "__root_genres"."id" AS "__root_genres_id",
       "__root_genres"."rawg_id" AS "__root_genres_rawg_id",
       "__root_genres"."name" AS "__root_genres_name",
       "__root_publishers"."id" AS "__root_publishers_id",
       "__root_publishers"."rawg_id" AS "__root_publishers_rawg_id",
       "__root_publishers"."name" AS "__root_publishers_name",
       "__root_tags"."id" AS "__root_tags_id",
       "__root_tags"."rawg_id" AS "__root_tags_rawg_id",
       "__root_tags"."name" AS "__root_tags_name",
       "__root_progresses"."id" AS "__root_progresses_id",
       "__root_progresses"."minutes_played" AS "__root_progresses_minutes_played",
       "__root_progresses"."state" AS "__root_progresses_state",
       "__root_progresses"."created_at" AS "__root_progresses_created_at",
       "__root_progresses"."updated_at" AS "__root_progresses_updated_at",
       "__root_progresses"."user_id" AS "__root_progresses_user_id",
       "__root_progresses"."game_id" AS "__root_progresses_game_id",
       "__root_box_image"."id" AS "__root_box_image_id",
       "__root_box_image"."source" AS "__root_box_image_source",
       "__root_box_image"."path" AS "__root_box_image_path",
       "__root_box_image"."media_type" AS "__root_box_image_media_type",
       "__root_background_image"."id" AS "__root_background_image_id",
       "__root_background_image"."source" AS "__root_background_image_source",
       "__root_background_image"."path" AS "__root_background_image_path",
       "__root_background_image"."media_type" AS "__root_background_image_media_type"
FROM "game" "__root"
LEFT JOIN "game_developers_developer" "__root___root_developers" ON "__root___root_developers"."game_id"="__root"."id"
LEFT JOIN "developer" "__root_developers" ON "__root_developers"."id"="__root___root_developers"."developer_id"
LEFT JOIN "game_genres_genre" "__root___root_genres" ON "__root___root_genres"."game_id"="__root"."id"
LEFT JOIN "genre" "__root_genres" ON "__root_genres"."id"="__root___root_genres"."genre_id"
LEFT JOIN "game_publishers_publisher" "__root___root_publishers" ON "__root___root_publishers"."game_id"="__root"."id"
LEFT JOIN "publisher" "__root_publishers" ON "__root_publishers"."id"="__root___root_publishers"."publisher_id"
LEFT JOIN "game_tags_tag" "__root___root_tags" ON "__root___root_tags"."game_id"="__root"."id"
LEFT JOIN "tag" "__root_tags" ON "__root_tags"."id"="__root___root_tags"."tag_id"
LEFT JOIN "progress" "__root_progresses" ON "__root_progresses"."game_id"="__root"."id"
LEFT JOIN "image" "__root_box_image" ON "__root_box_image"."id"="__root"."box_image_id"
LEFT JOIN "image" "__root_background_image" ON "__root_background_image"."id"="__root"."background_image_id"
WHERE "__root"."deleted_date" IS NULL
ORDER BY "__root"."id" ASC NULLS LAST
LIMIT 5

The Database returned the same game entry 5 times in a row:

[ppetzold]

you mentioned that maxLimit = 0 and query = 0 works correctly, right?

then this is a typeorm issue. see

seems like queryBuilder.getMany() generate correct sql but queryBuilder.getManyAndCount() does not.

would suggest to reduce the complexity of your joins. try which join makes typeorm fail.

[ppetzold]

@Alfagun74 would close this as it seems typeorm related, or any findings to think otherwise?

[Alfagun74]

I found nothing except typeorm/typeorm#2370. I played around with the joins but nothing worked except removing them entirely. Its sad that the PaginationType Option was removed. At least i had the option to use take/skip then. I didn't have any trouble with it like with Limit/Offset. :( Gotta stay on 5.1.1 i guess.

[ppetzold]

can you confirm tho that .getMany() generates a correct result set in your case but .getManyAndCount() does not?

[Alfagun74]

can you confirm tho that .getMany() generates a correct result set in your case but .getManyAndCount() does not?

What do i need to do to test it?

[ppetzold]

Try maxLimit=0 and in query ?limit=0

It is using .getMany()

otherwise

it uses .getManyAndCount()

[Alfagun74]

Oh.
Yeah then i can confirm getMany() works and getManyAndCount() does not.

maxLimit 0 as you suggested combined with query limit=0 returns all 399 entries for me.

[ppetzold]

Have you tried to remove some of your joins to make getManyAndCount() work as well?

...

I can add paginationType again but then with limit/offset as default and a warning that skip/take will make other features buggy.

[Alfagun74]

I played around with the joins but nothing worked except removing them entirely. Its sad that the PaginationType Option was removed.

Yeah that option would be useful!

[ppetzold]

Closed with v8.1

[Alfagun74]

Ehrenmann!