QueryBuilder using having with calculated column

[alexojegu]

Is your feature request related to a problem? Please describe.
The clausule having accept the use of calculated columns and QueryBuilder.having permit use QBFilterQuery sintaxis:

having(cond?: QBFilterQuery | string, params?: any[]): this;

But using this sintaxis don't permit use calculated columns:

public async findList(limit: number, cursor?: StudioStoreCursor, geometry?: RootResolverGeometry): Promise<StudioEntity[]> {
        const query = this.studioRepository
            .createQueryBuilder("e0")
            .select(
                `e0.*, ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON('${
                    JSON.stringify(geometry) || null
                }', 1, 4326)) as distance`,
            );

        if (cursor) {
            if (cursor.distance) {
                query.having({
                    distance: { $gte: cursor.distance },
                    $or: [{ distance: { $gt: cursor.distance } }, { id: { $lt: cursor.id } }],
                });
            } else {
                query.where({ id: { $lt: cursor.id } });
            }
        }

        return await query.orderBy({ distance: "asc", id: "desc" }).limit(limit).getResult();
    }

select e0.*, ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON('{"type":"Point","coordinates":[42.605556,-5.57]}', 1, 4326)) as distance from `studio` as `e0` having `e0`.`distance` >= 5502111.294724894 and (`e0`.`distance` > 5502111.294724894 or `e0`.`id` < 24) order by `distance` asc, `e0`.`id` desc limit 6

select e0.*, ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON('{"type":"Point","coordinates":[42.605556,-5.57]}', 1, 4326)) as distance from studio as e0 having e0.distance >= 5502111.294724894 and (e0.distance > 5502111.294724894 or e0.id < 24) order by distance asc, e0.id desc limit 6 - Unknown column 'e0.distance' in 'having clause'

Describe the solution you'd like
If the column is marked as persist: false in the entity use the column as is, in this case, distance and not e0.distance. Or maybe require a prefix, for example "$", to indicate that is a calculated column, in this case, query.having({ $distance: { $gte: cursor.distance }).

Describe alternatives you've considered
Use the string sintaxis:

query.having("distance >= ? and (distance > ? or id < ?)", [
    cursor.distance,
    cursor.distance,
    cursor.id,
]);

Additional context
I like the QBFilterQuery syntax better and it is the one I use in other queries so I would like to maintain consistency.

[B4nan]

I think this should actually work, but you need to define the distance property in your entity first, as persist: false.

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

Otherwise it would not be even mapped to your entity when doing qb.getResult().

[alexojegu]

Sorry, I forgot to add the entity to the issue:

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

    @Property({ columnType: "point srid 4326" })
    public position!: unknown;

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

As you can see, I already have define the distance property.

[alexojegu]

If orderBy permit calculated columns and practically the only difference is it call to QueryHelper.processWhere. Could it be as easy to fix as change?

mikro-orm/packages/knex/src/query/QueryBuilder.ts

Lines 159 to 166 in 0474787

	having(cond: QBFilterQuery | string = {}, params?: any[]): this {
	if (Utils.isString(cond)) {
	cond = { [`(${cond})`]: Utils.asArray(params) };
	}
	this._having = CriteriaNode.create(this.metadata, this.entityName, cond).process(this);
	return this;
	}

by this:

  having(cond: QBFilterQuery | string, params?: any[]): this {
    cond = QueryHelper.processWhere(cond as Dictionary, this.entityName, this.metadata);

    if (Utils.isString(cond)) {
      cond = { [`(${cond})`]: Utils.asArray(params) };
    }

    this._having = CriteriaNode.create(this.metadata, this.entityName, cond).process(this);
    return this;
  }

[B4nan]

Interesting, it is actually caused by the complex query condition, it works fine with just { distance: ... }, but when you nest that inside $or, it will get prefixed.

[erickGarciaBallastlane]

worked like charm!! I added a new column used by the orderBy, in my entity and now works, THANK YOU!