Mercurial > dive4elements > river
changeset 9218:13b764afb536
Bed height queries extended by km range condition
author | mschaefer |
---|---|
date | Wed, 04 Jul 2018 10:36:54 +0200 (2018-07-04) |
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();