How to order by Query dsl NumberExpression column

Question

In mariadb, I created the function to calucate distance between user point and place point

    /* create function */
    DELIMITER $$

    CREATE
    FUNCTION `u_st_distance_sphere`(`user_point` POINT, `place_point` POINT)
    RETURNS double
    BEGIN
    return (6371*acos(cos(radians(ST_X(user_point)))*cos(radians(ST_X(place_point)))*cos(radians(ST_Y(place_point))
        -radians(ST_Y(user_point)))+sin(radians(ST_X(user_point)))*sin(radians(ST_X(place_point)))));
    END$$
    DELIMITER ;

To use the function, register function in CustomDialect class and apply in .yml

public class MariadbCustomDialect extends MariaDB103Dialect {
    public MariadbCustomDialect() {
        super();
        this.registerFunction(
                "u_st_distance_sphere",
                new StandardSQLFunction("u_st_distance_sphere", StandardBasicTypes.DOUBLE)
        );
    }
}

spring:
  ...
  jpa:
    ...
    database-platform: ...global.config.MariadbCustomDialect

And I want to make distance column that calculated with points and sort by distance ascending order

//in PlaceCustomRepository
//making filtered JPQLQuery

String pointWKT = String.format("POINT(%s %s)", 
                                 placeFilterParam.getLatitude(),
                                 placeFilterParam.getLongitude());

NumberTemplate<Double> expr = Expressions.numberTemplate(
    Double.class, 
    "u_st_distance_sphere( ST_GeomFromText({0}), ST_GeomFromText({1}) )", 
    pointWKT, 
    place.point);

JPQLQuery<PlaceResponseTest> query = jpaQueryFactory
    .select(
            new QPlaceResponseTest(place.id, 
                                   expr.as("distance")))
    .from(place)
    .orderBy();

How to specify distance column??

Thanks

How to order by Query dsl NumberExpression column

0 Answers0