package com.d3vmoon.at.service;

import com.google.common.collect.ImmutableMap;
import org.postgresql.geometric.PGpoint;
import spark.Request;
import spark.Response;

import java.util.List;
import java.util.Map;

import static com.d3vmoon.at.db.Tables.AT_TRAIL;
import static org.jooq.impl.DSL.*;

public class ATService extends AbstractService {

    public List<Map<String, Double>> getCurrentTrail(Request req, Response resp) {
    return ctx.select(AT_TRAIL.POINT)
            .from(AT_TRAIL)
            .where(AT_TRAIL.ID.le(
                    ctx.select(field("id", Integer.class))
                    .from(
                            ctx.select(AT_TRAIL.ID, AT_TRAIL.POINT, field(
                                    "point <-> ( " +
                                    "  select point " +
                                    "    from at_shelter  " +
                                    "   where id = ( " +
                                    "          select at_shelter " +
                                    "            from at_last_shelter " +
                                    "        ) "+
                                    ")"
                            ).as("dist"))
                            .from(AT_TRAIL)
                            .orderBy(field("dist").asc())
                            .limit(1)
                    )
            ))
            .orderBy(AT_TRAIL.ID.asc())
            .fetch()
            .map(r -> {
                PGpoint point = r.get(AT_TRAIL.POINT, PGpoint.class);
                return ImmutableMap.of("lat", point.x, "lng", point.y);
            });
    }
}

@@ -206,16 +206,123 @@
 l.Map;%0A%0A
+import static com.d3vmoon.at.db.Tables.AT_LAST_SHELTER;%0Aimport static com.d3vmoon.at.db.Tables.AT_SHELTER;%0A
 import s
@@ -362,16 +362,16 @@
 _TRAIL;%0A
-
 import s
@@ -656,45 +656,26 @@
     
-ctx.
 select(
-field(%22id%22, Integer.class)
+AT_TRAIL.ID
 )%0A  
@@ -698,25 +698,26 @@
   .from(
-%0A        
+AT_TRAIL)%0A
         
@@ -732,63 +732,42 @@
     
-ctx.select(AT_TRAIL.ID, AT_TRAIL.POINT, field(%0A        
+.orderBy(field(%22%7B0%7D %3C-%3E ( %7B1%7D )%22,%0A
     
@@ -794,24 +794,23 @@
     
-%22point %3C-%3E ( %22 +
+AT_TRAIL.POINT,
 %0A   
@@ -838,35 +838,32 @@
     
-        %22  select point %22 +
+select(AT_SHELTER.POINT)
 %0A   
@@ -891,41 +891,25 @@
     
-        %22    from at_shelter  %22 +
+.from(AT_SHELTER)
 %0A   
@@ -937,36 +937,32 @@
     
-        %22   where id = ( %22 +
+.where(AT_SHELTER.ID.eq(
 %0A   
@@ -998,40 +998,42 @@
     
-%22          select at_shelter %22 +
+select(AT_LAST_SHELTER.AT_SHELTER)
 %0A   
@@ -1069,135 +1069,30 @@
     
-%22            from at_last_shelter %22 +%0A                                    %22        ) %22+%0A                                    %22)%22
+.from(AT_LAST_SHELTER)
 %0A   
@@ -1120,19 +1120,8 @@
     
-).as(%22dist%22
 ))%0A 
@@ -1143,81 +1143,8 @@
     
-        .from(AT_TRAIL)%0A                            .orderBy(field(%22dist%22
 ).as
@@ -1144,32 +1144,24 @@
    ).asc())%0A
-        
             
@@ -1182,30 +1182,8 @@
 (1)%0A
-                    )%0A
     

simplified query