raw() in orderBy in decorator throws error: column [raw] does not exist

[squivix]

Describe the bug

When using the raw() helper function in the orderBy field of the decorator, it generates an invalid sql which results in an error: column [raw] does not exist.

Example:

@Entity()
export class EntityA {
    @PrimaryKey({type: types.integer})
    id!: number;

    @OneToMany({
        entity: () => EntityB, mappedBy: (entityB: EntityB) => entityB.entityA,
        orderBy: {
            [raw("LENGTH(text)")]: "asc"
        }
    })
    orderedEntityBs: Collection<EntityB> = new Collection<EntityB>(this);
}

@Entity()
export class EntityB {
    @PrimaryKey({type: types.integer})
    id!: number;

    @Property({type: types.string, length: 500})
    text!: string;

    @ManyToOne({entity: () => EntityA, inversedBy: (entityA) => entityA.entityBs, deleteRule: "cascade", updateRule: "cascade"})
    entityA!: Ref<EntityA>;
}

Then trying to query:

await em.find(EntityA, {}, {
        populate: ["orderedEntityBs"]
    });

throws error:

InvalidFieldNameException: select `e0`.*, `o1`.`id` as `o1__id`, `o1`.`text` as `o1__text`, `o1`.`entity_a_id` as `o1__entity_a_id` from `entity_a` as `e0` left join `entity_b` as `o1` on `e0`.`id` = `o1`.`entity_a_id` order by `o1`.`[raw]` asc - SQLITE_ERROR: no such column: o1.[raw]

Meanwhile using the same raw() in the orderBy of find functions works perfectly fine as seen in repro.

Note that expr in MikroORM v5 didn't have the same problem.

Reproduction

Repro

What driver are you using?

@mikro-orm/sqlite

MikroORM version

6.1.6-dev.1

Node.js version

v18.18.0

Operating system

Linux

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Slack.
• The provided reproduction is a minimal reproducible example of the bug.

[B4nan]

I'm afraid supporting this properly might be a wontfix, the way things are currently designed allows only a single use of a raw fragment as a key, so even if we fix this, it would only work once per the whole app context, not just fork based. I'll first try to fix the single usage, and we could add a callback signature for the orderBy (as well as the where) option, that's the only way to properly fix that.

edit: Maybe there is another way around, we could allow marking raw fragments as global and those would be never removed from the map, something like raw('...', { global: true }), I'll experiment with that a bit first.
edit2: Hmm or maybe this can be even simpler, as we can detect those raw fragments are coming from the property options, looks like I was just overthinking this.

---

But there is an alternative approach you can use right now - with a formula property for the length that you can use for ordering:

@Formula(a => `length(${a}.text)`)
textLength!: number;

// ...

@OneToMany({
    entity: () => EntityB, mappedBy: (entityB: EntityB) => entityB.entityA,
    orderBy: {
        textLength: "asc"
    }
})
orderedEntityBs: Collection<EntityB> = new Collection<EntityB>(this);

[squivix]

I'm wondering why expr() worked before in v5 🤔? Was it implemented differently?

I like the @Formula workaround though.

[B4nan]

expr was always just a type helper, it didnt have any effect on runtime