Support nullable values in cursor pagination

[atfera]

Is your feature request related to a problem? Please describe.

Hello,
I'm running into an issue when trying to use cursor pagination with columns that can have null values, even though I'm also using a unique non nullable column to build the Cursor (like id column).

I tried to do some research about it, and yeah it's not easy to implement, mainly because databases handle nulls differently.
And I also noticed that ordering with NULL FIRST | LAST doesn't work in mikro-orm with the cursor pagination.

For the moment, I switched to offset pagination to make it work.

Maybe the docs need to be updated to precise that it must be ordered by non-null columns.

Reproduction

import { Entity, PrimaryKey, Property } from '@mikro-orm/core';

@Entity()
export class User {

  @PrimaryKey()
  id!: number;

  @Property()
  name!: string;

  @Property({ nullable: true })
  age?: number | null;
}

import { MikroORM, QueryOrder } from '@mikro-orm/sqlite';
import { User } from './user.entity';
import { faker } from '@faker-js/faker';

let orm: MikroORM;

beforeAll(async () => {
  orm = await MikroORM.init({
    dbName: 'sqlite.db',
    entities: ['dist/**/*.entity.js'],
    entitiesTs: ['src/**/*.entity.ts'],
    debug: ['query', 'query-params'],
    allowGlobalContext: true, // only for testing
  });
  await orm.schema.refreshDatabase();
});

afterAll(async () => {
  await orm.close(true);
});

test('cursor pagination', async () => {
  Array.from({ length: 500 }).forEach(() => {
    orm.em.create(User, {
      name: faker.person.fullName(),
      age: Math.floor(Math.random() * 10) ? faker.number.int({max: 100, min: 0}) : null
    });
  });

  await orm.em.flush();

  const res = await orm.em.findByCursor(User, {}, {
    first: 100,
    orderBy: {
      age: QueryOrder.ASC,
      id: QueryOrder.ASC,
    }
  });
  console.log(res)
});

Error:

 CursorError: Cannot create cursor, value for 'User.age' is missing.

[B4nan]

One possible workaround could be using a formula property and adding a fallback to the null values (and using that for ordering instead of the nullable column).