feathers fix(knex): Fix total calculation

fix(knex): Fix total calculation

Open CITIZENDOT opened this issue 1 year ago • 1 comments

[X] Tell us about the problem your pull request is solving.
- When the query contains DISTINCT or GROUP BY, incorrect total is being returned. This PR fixes it by calculating the count using a subquery. More details below:
- When DISTINCT clause is used, clearSelect() removes the DISTINCT clause in the countBuilder query, hence returning non-distinct count. For eg:
  - ```
  SELECT DISTINCT `users.*` FROM `users` WHERE ....
```
- is converted to
- ```
SELECT `*` FROM `users` WHERE ...
```
  - losing the distinct property of the query.
- When GROUP BY clause is used, GROUP BY is applied on COUNT(`${name}.${id}`), which is useless, because rows returned by COUNT will not contain any column other than total to GROUP BY. Hence non-distinct rows are returned again.
- Hence the solution is, to wrap the main query into a subquery, and count the rows returned by it, which is what this PR does.
[ ] Are there any open issues that are related to this?
- No. But this is a bug, I can create an issue if necessary.
[ ] Is this PR dependent on PRs in other repos?
- No

Aug 15 '24 11:08 CITIZENDOT