Query with join, orderBy and limit not working anymore with version 5.1.0 (postgres)

[co-sic]

Describe the bug
After the upgrade from version 5.0.5 to 5.1.0 my query is no longer working and throws this error: function min(uuid) does not exist (see stracktrace below). Minimal example:

@Entity()
export class Customer extends BaseEntity<Customer, 'id'> {
  @PrimaryKey({ type: 'uuid', defaultRaw: 'gen_random_uuid()' })
  id!: string;

  @Property({ type: Date })
  @Index()
  createdAt: Date;

  @Property()
  customerNumber: string;

  @OneToMany(() => Contract, (contract) => contract.customer)
  contracts = new Collection<Contract>(this);

  constructor(customerNumber: string) {
    super();
    this.customerNumber = customerNumber;
    this.createdAt = new Date();
  }
}

@Entity()
export class Contract extends BaseEntity<Contract, 'id'> {
  @PrimaryKey({ type: 'uuid', defaultRaw: 'gen_random_uuid()' })
  id!: string;

  @ManyToOne()
  customer: Customer;

  constructor(customer: Customer) {
    super();
    this.customer = customer;
  }
}

const result = await orm.entityManager.find(
      Customer,
      { contracts: { $ne: null } },
      { orderBy: { createdAt: QueryOrder.DESC, id: QueryOrder.DESC }, limit: 10 },
);

if i leave out either one of: orderBy, limit or the query that requires a join, it works, but with all of these 3 together it does not work. The resulting query also got considerably longer/more complex (see stack trace below), with v5.0.5 the query looked like this:

select "c0".* from "customer" as "c0" left join "contract" as "c1" on "c0"."id" = "c1"."customer_id" where "c1"."id" is not null order by "c0"."created_at" desc, "c0"."id" desc limit 10

Stack trace
DriverException: select "c0".* from "customer" as "c0" left join "contract" as "c1" on "c0"."id" = "c1"."customer_id" where "c0"."id" in (select "c0"."id" from (select "c0"."id" from "customer" as "c0" left join "contract" as "c1" on "c0"."id" = "c1"."customer_id" where "c1"."id" is not null group by "c0"."id" order by min("c0"."created_at") desc, min("c0"."id") desc limit 10) as "c0") order by "c0"."created_at" desc, "c0"."id" desc - function min(uuid) does not exist
at PostgreSqlExceptionConverter.convertException (/home/patrick/development/gastromatic/contract-service/node_modules/@mikro-orm/core/platforms/ExceptionConverter.js:8:16)
at PostgreSqlExceptionConverter.convertException (/home/patrick/development/gastromatic/contract-service/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:42:22)
at PostgreSqlDriver.convertException (/home/patrick/development/gastromatic/contract-service/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:173:54)
at /home/patrick/development/gastromatic/contract-service/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:177:24
at processTicksAndRejections (node:internal/process/task_queues:96:5)
at PostgreSqlDriver.find (/home/patrick/development/gastromatic/contract-service/node_modules/@mikro-orm/knex/AbstractSqlDriver.js:46:24)
at SqlEntityManager.find (/home/patrick/development/gastromatic/contract-service/node_modules/@mikro-orm/core/EntityManager.js:105:25)
at /home/patrick/development/gastromatic/contract-service/src/app.ts:25:20

**Versions**

| Dependency | Version |
| - | - |
| node | 16 |
| typescript | 4.5.5 |
| mikro-orm | 5.1.0 |

[co-sic]

might be related to this change #2835 ?

[B4nan]

Nope, this is automatic pagination flag. As you join to-many relation, the number of rows will be higher than just the number of root table rows - you cant just apply a limit on them.

You can disable pagination via:

const result = await orm.entityManager.find(
  Customer,
  { contracts: { $ne: null } },
  {
    orderBy: { createdAt: QueryOrder.DESC, id: QueryOrder.DESC },
    limit: 10,
    flags: [QueryFlag.DISABLE_PAGINATE],
  },
);

But I doubt the way it was before could give you correct results. Imagine your first root entity has 10 items in the joined to-many collection - you end up querying first 10 rows, which are all about the first root entity, so in fact selecting just one instead of 10 entities.

[B4nan]

Looks like postgres does not support min/max on UUIDs, that's dumb. Fortunately we can cast it to ::text to get around it, but can't say I am happy to add new abstraction there because of this :D

[co-sic]

Hey, thank you for the quick reply, this works! (Edit: im refering to your first reply, didn't see the second one until know)
I didn't know about the Paginated mode until know, but is is not relevant for me anyway i think, since we are doing a cursor based pagination and not offset, so i'm using two separate queries to get the results and the total count and can't go for findAndCount.

About the query, you a right, i didn't think this through. I didn't notice this until know, since a customer usually has 1 or 0 contracts, but i just checked, if e.g. one customer has 2 contracts, i only get 9 results instead of 10, which makes sense. Thank you for pointing that out!

But is there a way to achieve what i want with the mikro-orm query language? (i want only customer entities, where there are no existing contract's). I also have some other use cases i will need in the future, like if i stay in this example, get only customers that have at least one contract. Or to go even further, get all customers where at least on contract matches a given sub query.
I'm working with prisma in another project and they have this functionality with a "some/none/every" filter prisma docs. Not sure if something like this is currently possible with mikro-orm (without writing custom sql with knex), or if it is in the scope for future updates to this library.

[B4nan]

but is is not relevant for me anyway i think, since we are doing a cursor based pagination and not offset

If you use either limit or offset in the query where you join to-many relation, it does affect you. The query you have in the OP is doing that and will return wrong results without the pagination.

I'm working with prisma in another project and they have this functionality with a "some/none/every" filter prisma docs. Not sure if something like this is currently possible with mikro-orm (without writing custom sql with knex), or if it is in the scope for future updates to this library.

Nothing implemented, but it is indeed one of the things I would like to have in future, probably as operators, we could have $size or $count which should cover all of some/none/every as you could use operators on top of that, to do { tags: { $size: 0 } } or { tags: { $size: { $ne: 0 } } }.

What queries does prisma generate for this? Do they use subqueries or some join magic with aggregations?

[jnsvu]

Having the same issue when ordering by boolean.
function min(boolean) does not exist

[B4nan]

Should be addressed in next dev version.