Paginate returns unexpected number resources when using `limit` with `sortBy` relation's field

[garrick-lam]

Sample code and test to demonstrate the issue: https://github.com/garrick-lam/nest-demo1/blob/master/test/app.e2e-spec.ts#L31-L44

In the second test, sortBy parameter is a relation's field posts.id, it returns unexpected (less) number of resources.

Executed SQL queries

SELECT
    DISTINCT `distinctAlias`.`__root_id` AS `ids___root_id`,
    `distinctAlias`.`__root_posts_id`
FROM
    (
    SELECT
        `__root`.`id` AS `__root_id`,
        `__root`.`firstName` AS `__root_firstName`,
        `__root`.`lastName` AS `__root_lastName`,
        `__root`.`age` AS `__root_age`,
        `__root_posts`.`id` AS `__root_posts_id`,
        `__root_posts`.`title` AS `__root_posts_title`,
        `__root_posts`.`description` AS `__root_posts_description`,
        `__root_posts_postReplys`.`title` AS `__root_posts_postReplys_title`
    FROM
        `user` `__root`
    LEFT JOIN `post` `__root_posts` ON
        `__root_posts`.`userId` = `__root`.`id`
    LEFT JOIN `post_reply` `__root_posts_postReplys` ON
        `__root_posts_postReplys`.`postId` = `__root_posts`.`id`) `distinctAlias`
ORDER BY
    `distinctAlias`.`__root_posts_id` ASC,
    `__root_id` ASC
LIMIT 5

SELECT
    `__root`.`id` AS `__root_id`,
    `__root`.`firstName` AS `__root_firstName`,
    `__root`.`lastName` AS `__root_lastName`,
    `__root`.`age` AS `__root_age`,
    `__root_posts`.`id` AS `__root_posts_id`,
    `__root_posts`.`title` AS `__root_posts_title`,
    `__root_posts`.`description` AS `__root_posts_description`,
    `__root_posts_postReplys`.`title` AS `__root_posts_postReplys_title`
FROM
    `user` `__root`
LEFT JOIN `post` `__root_posts` ON
    `__root_posts`.`userId` = `__root`.`id`
LEFT JOIN `post_reply` `__root_posts_postReplys` ON
    `__root_posts_postReplys`.`postId` = `__root_posts`.`id`
WHERE
    `__root`.`id` IN (15, 14, 14, 15, 16)
ORDER BY
    `__root_posts_id` ASC

[ppetzold]

I think, this is related to a known typeorm issue using take/skip with orderBy on joins.

You can try using limit/offset (paginationType in conf) but that comes with other problems as well 🙈

[garrick-lam]

Thanks @ppetzold I tried to call paginate with paginationType: PaginationType.TAKE_AND_SKIP option, but no luck. The second query is still the same. It seems to me the problem can be fixed, if I can select the ids___root_id only in the first query:

SELECT
-   DISTINCT `distinctAlias`.`__root_id` AS `ids___root_id`,
+   DISTINCT `distinctAlias`.`__root_id` AS `ids___root_id`
-   `distinctAlias`.`__root_posts_id`

[ppetzold]

I was referring to LIMIT_AND_OFFSET. take/skip is default

[juventus18]

Same issue - only returning results until the relationship reaches the count. I'm a bit unclear, where do I change paginationType to try it out? (It doesn't appear to be an option on PaginateConfig, is the option newer than 4.7.0?)

Edit: nevermind, I see its in 8.1.0

[juventus18]

There is a TypeORM option called relationLoadStrategy which might help here. The default strategy is "joins", but perhaps "query" might work better here. My IDE hinting says

Specifies how relations must be loaded - using "joins" or separate queries. If you are loading too much data with nested joins it's better to load relations using separate queries.

Default strategy is "join", but this default can be changed here. Also, strategy can be set per-query in FindOptions and QueryBuilder.

I tried changing to "query", but it didn't seem to have any effect on the resulting query (same queries no matter which option I've set).

The hint says it can be set in the find options, is nestjs-paginate maybe setting it somewhere or otherwise not respecting the TypeORM configured value?

[profDevA]

Is this not fixed yet? I should use relation and sort by relation field. but it returns always data.. even when it should return empty data