Mikro-orm order by ST_Distance_Sphere using MySQL driver

Question

With MySQL, I am trying to order by ST_Distance_Sphere using QueryBuilder.

I have a entity:

import { Entity, PrimaryKey, Property } from "mikro-orm";

@Entity({ tableName: "studio" })
export default class StudioEntity {
    @PrimaryKey()
    public id!: number;

    @Property()
    public name!: string;

    @Property({ columnType: "point srid 4326" })
    public geometry!: object;
}

And I am trying:

export default class StudioStore {
    private studioRepository: EntityRepository<StudioEntity>;

    public constructor(ormClient: OrmClient) {
        this.studioRepository = ormClient.em.getRepository(StudioEntity);
    }

    public async findPage(first: number): Promise<StudioEntity[]> {
        const query = this.studioRepository.createQueryBuilder().select("*");

        query.addSelect(
            "ST_Distance_Sphere(`e0`.`geometry`, ST_GeomFromText('POINT(28.612849 77.229883)', 4326)) as distance",
        );

        query.orderBy({ distance: "ASC" });

        return query.limit(first).getResult();
    }
}

But I get a ORM error:

Trying to query by not existing property StudioEntity.distance

So, I try to add a property to the entity:

    @Property({ persist: false })
    public distance?: number;

But now I get a MySQL error:

Unknown column 'e0.distance' in 'order clause'

This is the generated SQL query:

[query] select `e0`.*, ST_Distance_Sphere(`e0`.`geometry`, ST_GeomFromText('POINT(28.612849 77.229883)', 4326)) as distance from `studio` as `e0` order by `e0`.`distance` asc limit 5 [took 4 ms]

Answer 1

Nowadays (v6) you can use a custom type with convertToDatabaseValueSQL method:

https://mikro-orm.io/docs/custom-types#advanced-example---pointtype-and-wkt

---

You will need to fallback to knex, as QB currently supports only defined property fields in order by. You will also need to define that virtual distance property as you already did, so the value can be mapped to the entity.

https://mikro-orm.io/docs/query-builder/#using-knexjs

const query = this.studioRepository.createQueryBuilder().select("*");
query.addSelect("ST_Distance_Sphere(`e0`.`geometry`, ST_GeomFromText('POINT(28.612849 77.229883)', 4326)) as distance");
query.limit(first);
const knex = query.getKnexQuery();
knex.orderBy('distance', 'asc');
const res = await this.em.getConnection().execute(knex);
const entities = res.map(a => this.em.map(StudioEntity, a));

---

Not very nice I must say, totally forgot that it is possible to order by computed fields. Will try to address this in v4. I think it could even work as your second approach, as QB could simply check if the property is virtual (has persist: false), and then it would not prefix it.

edit: as of 3.6.6 the approach with persist: false should work out of box