Setting limit while using findAndCount() always returns one result even if more are available

[suavelizard]

Describe the bug
Setting limit while using findAndCount always returns one result even if more are available.

To Reproduce
Steps to reproduce the behavior:

const [results, count]= await this.fooRepository.findAndCount(
      {
        $and: [{ organization: organization }, {}],
      },
      {
         limit: 5
      },
    );
console.log(results.length, count);

Output: 1 7

Generated SQL

select
    "r0" ."uuid",
    "r0" ."created_at",
    "r0" ."updated_at",
    "r0" ."deleted_at",
    "r0" ."slug",
    "r0" ."title",
    "r0" ."description",
    "r0" ."created_by_uuid",
    "r0" ."organization_uuid",
    "c1" ."uuid" as "c1__uuid",
    "c1" ."created_at" as "c1__created_at",
    "c1" ."updated_at" as "c1__updated_at",
    "c1" ."deleted_at" as "c1__deleted_at",
    "c1" ."body" as "c1__body",
    "c1" ."author_uuid" as "c1__author_uuid",
    "c1" ."target_uuid" as "c1__target_uuid",
    "s2" ."uuid" as "s2__uuid",
    "s2" ."created_at" as "s2__created_at",
    "s2" ."updated_at" as "s2__updated_at",
    "s2" ."deleted_at" as "s2__deleted_at",
    "s2" ."article_uuid" as "s2__article_uuid",
    "s2" ."order" as "s2__order",
    "s2" ."description" as "s2__description",
    "s2" ."title" as "s2__title"
from
    "article" as "r0"
    left join "comment" as "c1" on "r0" ."uuid" = "c1" ."target_uuid"
    left join "article_step" as "s2" on "r0" ."uuid" = "s2" ."article_uuid"
where
    "r0" ."organization_uuid" = '371245b2-e60d-401c-bcd0-b42860916a32'
limit
    5

Expected behavior
Limit should return up to 5 items if they are available not one.

Additional context
Removing limit returns all 8 results.

Versions

Dependency	Version
node	v16.13.1
typescript	"typescript": "^4.3.5"
mikro-orm	"@mikro-orm/core": "^5.0.3"
your-driver	"@mikro-orm/postgresql": "^5.0.3"

[B4nan]

Please always upgrade to latest before filing issues, and note that you are supposed to say what exact versions are you using - so not a range, an exact version.

This has been fixed in v5.0.5.

[suavelizard]

I might be doing something wrong but I just upgraded to 5.0.5 and purged node_modules

select
    "r0" ."uuid",
    "r0" ."created_at",
    "r0" ."updated_at",
    "r0" ."deleted_at",
    "r0" ."slug",
    "r0" ."title",
    "r0" ."description",
    "r0" ."created_by_uuid",
    "r0" ."organization_uuid",
    "c1" ."uuid" as "c1__uuid",
    "c1" ."created_at" as "c1__created_at",
    "c1" ."updated_at" as "c1__updated_at",
    "c1" ."deleted_at" as "c1__deleted_at",
    "c1" ."body" as "c1__body",
    "c1" ."author_uuid" as "c1__author_uuid",
    "c1" ."target_uuid" as "c1__target_uuid",
    "s2" ."uuid" as "s2__uuid",
    "s2" ."created_at" as "s2__created_at",
    "s2" ."updated_at" as "s2__updated_at",
    "s2" ."deleted_at" as "s2__deleted_at",
    "s2" ."runbook_uuid" as "s2__runbook_uuid",
    "s2" ."order" as "s2__order",
    "s2" ."description" as "s2__description",
    "s2" ."title" as "s2__title"
from
    "runbook" as "r0"
    left join "comment" as "c1" on "r0" ."uuid" = "c1" ."target_uuid"
    left join "runbook_step" as "s2" on "r0" ."uuid" = "s2" ."runbook_uuid"
where
    "r0" ."organization_uuid" = '371245b2-e60d-401c-bcd0-b42860916a32'
limit
    10	

I can see this generated query returning 10 of the same row with the same uuid in postgres and one row via the ORM
The logged output of count is 7 and I see 7 rows matching in postgres when using

select
    "r0" ."uuid",
    "r0" ."created_at",
    "r0" ."updated_at",
    "r0" ."deleted_at",
    "r0" ."slug",
    "r0" ."title",
    "r0" ."description",
    "r0" ."created_by_uuid",
    "r0" ."organization_uuid"
from
    "runbook" as "r0"

where
    "r0" ."organization_uuid" = '371245b2-e60d-401c-bcd0-b42860916a32'
limit
    10

Versions

Dependency	Version
node	v16.13.1
typescript	4.6.2
mikro-orm	5.0.5
your-driver	postgresql@5.0.5

[B4nan]

Can you try this?

const [results, count]= await this.fooRepository.findAndCount(
      {
        $and: [{ organization: organization }, {}],
      },
      {
         limit: 5,
         flags: [QueryFlag.PAGINATE],
      },
    );
console.log(results.length, count);

The query needs to be a subquery, which is what the paginate flag does. It should be applied automatically in latest version, but apparently only when using QB directly.

[suavelizard]

Yeah that works. Interesting. Thank you.

[B4nan]

Ok let's reopen then, this should work automatically nowadays.