# HG changeset patch
# User mschaefer
# Date 1530693414 -7200
# Node ID 13b764afb536cca5fefb2649da526c1785bf7189
# Parent  4e6111f87ddacdc4a07776460f3947a97975aab2
Bed height queries extended by km range condition

diff -r 4e6111f87dda -r 13b764afb536 backend/src/main/java/org/dive4elements/river/model/BedHeight.java
--- 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();