changeset 9218:13b764afb536

Bed height queries extended by km range condition
author mschaefer
date Wed, 04 Jul 2018 10:36:54 +0200
parents 4e6111f87dda
children 8642a76f22be
files backend/src/main/java/org/dive4elements/river/model/BedHeight.java
diffstat 1 files changed, 14 insertions(+), 5 deletions(-) [+]
line wrap: on
line diff
--- a/backend/src/main/java/org/dive4elements/river/model/BedHeight.java	Wed Jul 04 10:32:18 2018 +0200
+++ b/backend/src/main/java/org/dive4elements/river/model/BedHeight.java	Wed Jul 04 10:36:54 2018 +0200
@@ -208,11 +208,15 @@
     public static List<BedHeight> getBedHeights(final River river, final double kmLo, final double kmHi) {
         final Session session = SessionHolder.HOLDER.get();
 
-        final Query query = session.createQuery("from BedHeight where river=:river");
+        final Query query = session.createQuery("from BedHeight"
+                + " where river=:river"
+                + " and id in (select bedHeight.id from BedHeightValue"
+                + " where station between :kmfrom and :kmto"
+                + " group by bedHeight.id)");
 
         query.setParameter("river", river);
-
-        // TODO Do km range filtering in SQL statement
+        query.setParameter("kmfrom", kmLo);
+        query.setParameter("kmto", kmHi);
 
         final List<BedHeight> singles = query.list();
         final List<BedHeight> good = new ArrayList<>();
@@ -243,14 +247,19 @@
         return ((singles != null) && !singles.isEmpty()) ? singles.get(0) : null;
     }
 
-    // TODO Do km range filtering in SQL statement
     public static BedHeight getBedHeightByDescription(final River river, final String description, final double startKm, final double endKm) {
 
         final Session session = SessionHolder.HOLDER.get();
 
-        final Query query = session.createQuery("FROM BedHeight WHERE (trim(description)=:description) AND river=:river");
+        final Query query = session.createQuery("FROM BedHeight"
+                + " WHERE (TRIM(description)=:description) AND river=:river"
+                + " AND id IN (SELECT bedHeight.id FROM BedHeightValue"
+                + " WHERE station BETWEEN :kmfrom AND :kmto"
+                + " GROUP BY bedHeight.id)");
         query.setParameter("river", river);
         query.setParameter("description", description);
+        query.setParameter("kmfrom", startKm);
+        query.setParameter("kmto", endKm);
 
         final List<BedHeight> singles = query.list();
 

http://dive4elements.wald.intevation.org