Incorrect SQL query when using nested orderBy

[jaakdentrekhaak]

Steps to reproduce

NOTE: base.em = EntityManager<PostgreSqlDriver>

Soft-deletion enabled

const t0s = await base.em.find(
  T0,
  {},
  {
    orderBy: {
      r: {
        u: {
          f: QueryOrder.asc,
        },
      },
    },
  },
);

results in

select
  "t0".*,
  "t1"."id" as "t1__id",
  "r2"."id" as "r2__id",
from
  "t0" as "t0"
  inner join "t1" as "t1" on "t0"."t1_id" = "t1"."id"
  and "t1"."deleted_at" is null
  inner join "r" as "r2" on "t0"."r_id" = "r2"."id"
  and "r2"."deleted_at" is null
  left join "u" as "u3" on "r2"."u_id" = "u3"."id"
where
  "t0"."deleted_at" is null
order by
  "u3"."f" asc,
  "r2"."f" asc

which is incorrect and gives the error column r2.f does not exist because of the last line.

Soft-deletion disabled

const t0s = await base.em.find(
  T0,
  {},
  {
    orderBy: {
      r: {
        u: {
          f: QueryOrder.asc,
        },
      },
    },
    filters: {
      [SOFT_DELETABLE_FILTER]: false,
    },
  },
);

results in

select
  "t0".*
from
  "t0" as "t0"
  left join "r" as "r1" on "t0"."r_id" = "r1"."id"
  left join "u" as "u2" on "r1"."u" = "u2"."id"
order by
  "u2"."f" asc

which is correct and the expected behavior.

Entities

T0

@SoftDeletable(() => T0, 'deletedAt', () => new Date())
@Entity()
export class T0 {
  @PrimaryKey()
  id: string = crypto.randomUUID();

  @ManyToOne(() => T1, { deleteRule: 'cascade' })
  public t1: Rel<T1>;

  @ManyToOne(() => R, { deleteRule: 'cascade' })
  public r: Rel<R>;
}

T1

@SoftDeletable(() => T1, 'deletedAt', () => new Date())
@Entity()
export class T1 {
  @PrimaryKey()
  id: string = crypto.randomUUID();
}

R

@SoftDeletable(() => R, 'deletedAt', () => new Date())
@Entity()
export class R {
  @PrimaryKey()
  id: string = crypto.randomUUID();

  @ManyToOne(() => U, { deleteRule: 'cascade' })
  u: Rel<U>;
}

U

@SoftDeletable(() => U, 'deletedAt', () => new Date())
@Entity()
export class U {
  @PrimaryKey()
  id: string = crypto.randomUUID();

  @Property({ nullable: true })
  f?: string;
}

Environment

• @mikro-orm/core: ^6.3.6
• @mikro-orm/postgresql: ^6.3.6
• mikro-orm-soft-delete: 1.0.0-alpha.1
• database: PostgreSQL