Wrong totalCount when using joins in QB

[rudolfmedula]

In this commit 890ee16#diff-c58e144dadf00250381ed55e6ce83245cda76aca84131ad494fd4911932f656f. a custom total count query was introduced instead of TypeORMs qb.getCount();

this query being SELECT COUNT(*) FROM (..) returns number of all rows returned by query which would be like (m*n* k...) depending on how many rows were joined

the original implementation of TypeORMs getCount does SELECT COUNT(DISTINCT("table"."id")) AS "cnt" FROM (..)
which returns correct number of entities even when using JOINs

[rudolfmedula]

I can provide failing test, but the issue is pretty obvious. I'm curious what was the motivation for not using TypeORMs qb.getCount ?

[bashleigh]

sure, I haven't looked into this yet, don't suppose you'd know a way in typeorm to get the primary key column?

[rudolfmedula]

well in TypeORM they are able to get the primary columns from the metadata https://github.com/typeorm/typeorm/blob/dd94c9d38d62c98f7afd5396abbc7e32ba689aad/src/query-builder/SelectQueryBuilder.ts#L1833
I think they have the count query figured out and available in the QB API. If you could help me understand the motivation for the custom count query, would help, thanks.

[bashleigh]

Yea we need a more advanced query which I was trying to implement for others which doesn't have the distinct func so adding that will solve your problem and enable more advanced queries for others!

[rudolfmedula]

@bashleigh I've added the failing test as promised. Hope it's helpful.

master...rudolfmedula:issues/627

[bashleigh]

Haven't forgotten, been pretty ill. Don't suppose you could make a PR for your changes? That way I might as well merge them into master and solve the problem there

[rudolfmedula]

sure https://github.com/nestjsx/nestjs-typeorm-paginate/pull/634/files

[gulaandrij]

is there any chance to fix it?

[NikolayYakovenko]

I ran into the same problem.
@bashleigh, do you have any information about solving this problem?

[bashleigh]

Honestly, I haven't had a lot of time to look into this unfortunately. Please feel free to have a go yourselves! Really sorry I've left it so long!

[ayeen]

@rudolfmedula @NikolayYakovenko use the below code snippet, I hope this will resolve your problem, this helped me with the same issue I had faced in my application
options.paginationType = PaginationTypeEnum.TAKE_AND_SKIP

[NikolayYakovenko]

@ayeen, thank you for your advice.

I know about this option and used it.
But in my case the result was not completely correct. When I set paginationType: PaginationTypeEnum.TAKE_AND_SKIP,
I've got the correct value (count) of items, but meta.totalItems was incorrect.
For example, items is an array of 5 elements (which is true), but meta.totalItems is 10.

[ayeen]

@ayeen, thank you for your advice.

I know about this option and used it. But in my case the result was not completely correct. When I set paginationType: PaginationTypeEnum.TAKE_AND_SKIP, I've got the correct value (count) of items, but meta.totalItems was incorrect. For example, items is an array of 5 elements (which is true), but meta.totalItems is 10.

@NikolayYakovenko this means you are missing something like you are query on the wrong table which shows the total of 10 items and in a result that shows 5 items from join table.

[FelipeGerolomo]

Same problem :(