通过jpql别名关键字多次使用表达式 [英] multiple use of expression via jpql alias keyword

问题描述

我正在使用带有postgresql服务器的spring数据，并且我想执行一些GPS数据范围查询.这意味着，在给定坐标i的情况下，我可以即时计算从入口到给定点的距离，并检查一定范围.

I'm using spring data with a postgresql server and i want to perform some GPS-data range queries. This means, given a coordinate i compute on the fly the distance from the entry to the given point and check for a certain range.

由于我也想订购有关距离的数据，而且我也想检索实际距离，因此在sql中，我将使用AS关键字仅计算一次表达式，然后在where和the中使用此辅助表达式分部分订购. 但是，到目前为止，我还没有弄清楚如何在jqpl中做到这一点.所以我的查询应该做这样的事情:

Since i also want to order my data regarding the distance and additionally i want to retrieve the actual distance too, in sql i would use the AS keyword to compute the expression only once and then use this auxiliary expression in the where and the order by part. However, so far I haven't yet figured out how to do this in jqpl. So my query should do something like this:

SELECT NEW Result(p, <distance-expression>) FROM MyModel p where <distance-expression> <= :rangeParam order by <distance-expression>

但是，恐怕每个条目的都会被多次评估，因此这会对查询的运行时间/响应时间产生负面影响.

however, i'm afraid that the will be evaluated more than once for each entry and so this will have a negative impact on the runtime/response time of the query.

jqpl中是否有任何方法可以使用AS关键字来避免对
的多次求值 <distance-expression>?

Is there any way in jqpl to use the AS keyword to avoid the multiple evaluation of
<distance-expression>?

最诚挚的问候

推荐答案

具有内部视图的本机查询应该可以完成工作.假设使用class Location(id, latitude, longitude)和 Haversine公式来查找大圆上的点之间的距离，则以下存储库带有自定义本机查询的方法声明就足够了:

A native query with an inner view should get the job done. Assuming class Location(id, latitude, longitude) and the Haversine formula for finding distances between points on great circles, the following repository method declaration with a custom native query should be sufficient:

@Query(nativeQuery = true
  , value = "SELECT "
          + "  r.id "
          + "  , r.latitude "
          + "  , r.longitude "
          + "FROM "
          + "  (SELECT "
          + "    l.id          AS id "
          + "    , l.latitude  AS latitude "
          + "    , l.longitude AS longitude "
          + "    , 2 * 6371 * ASIN(SQRT(POWER(SIN(RADIANS((l.latitude - ?1) / 2)), 2) + COS(RADIANS(l.latitude))*COS(RADIANS(?1))*POWER(SIN(RADIANS((l.longitude - ?2) / 2)), 2))) AS distance "
          + "  FROM "
          + "    location l) AS r "
          + "WHERE "
          + "  r.distance < ?3")
List<Location> findAllByProximity(BigDecimal latitude
                               , BigDecimal longitude
                               , BigDecimal distance);

在Github上 可用的示例 (假定为公制单位).

Sample available on Github as an example (metric units assumed).

注意:与JPQL相比，在示例中使用本机查询的原因是JPQL中不支持三角函数.如果表达式更简单并且可以使用本机JPQL函数进行编码，则可以将本机查询替换为JPA查询.

Note: The reason behind using a native query in the example as opposed to JPQL is the lack of support for trigonometric functions in JPQL. In cases where the expression is simpler and can be coded using native JPQL functions, the native query can be replaced with a JPA query.

这篇关于通过jpql别名关键字多次使用表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！