title: JPA Criteria Equivalent Query Subquery greatest or max
body: <p>This SQL query does exactly what is needed when executed. I am looking for the JPA Criteria equivalent? Basically gets the Device entity and the LATEST(greatest) GPS coord from a One-To-Many relationship</p>
  <pre><code>SELECT DISTINCT t0.DEVICE_I, t0.ACTIVE_S, t0.APP_CONFIG_I, t0.PREV_APP_CONFIG_I, 
  t0.APP_CONFIG_CONFIRMED_S, t0.APP_CONFIG_RECEIPT_D, t0.APP_CONFIG_SENT_S, t0.CHANNEL_X, 
  t0.CREATION_TS, t0.CREATION_USER_I, t0.DEST_QUEUE_C, t0.DIVISION_C, t0.LAN_IP_X, 
  t0.LAST_UPDATE_TS, t0.LAST_UPDATE_USER_I, t0.MOBILE_IP_X, t0.ORIGIN_MP_I, 
  t0.PREV_CHANNEL_X, t0.TELEPHONE_NUMBER_X, t0.VERSION_X, t1.DEVICE_I, t1.COORDINATE_I, 
  t1.CREATION_TS, t1.CREATION_USER_I, t1.GENERATED_TS, t1.GPGGA_X, t1.GPGSA_X, 
  t1.GPGSV_X, t1.GPRMC_X, t1.GPVTG_X, t1.LAST_UPDATE_TS, t1.LAST_UPDATE_USER_I, 
  t1.WORK_ORDER_NUMBER_I 
  
  FROM DEVICE t0, GPS_COORD t1 
  
  WHERE  t0.DEVICE_I = t1.DEVICE_I AND  
  t1.GENERATED_TS IN ( select max(GENERATED_TS)
         from GPS_COORD
        group by DEVICE_I )
  ORDER BY t1.DEVICE_I ASC, t1.GENERATED_TS DESC
  </code></pre>
  <p>The java code below is almost there and generates the following query which is missing joined fields needed from GpsCoord. These fields are included if a fetch is done, but the Join is necessary later in the where clause of the Subquery:</p>
  <pre><code>    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
  
      CriteriaQuery&lt;Device&gt; cq = criteriaBuilder.createQuery(Device.class);
  
      Root&lt;Device&gt; device = cq.from(Device.class);
      cq.distinct(true);
  
      Join&lt;Device, GpsCoord&gt; j = device.join(Device_.gpsCoords, JoinType.LEFT);
      //Fetch&lt;Device,GpsCoord&gt; f = device.fetch(Device_.gpsCoords);
      
      CriteriaQuery&lt;Device&gt; select = cq.select(device);
  
  
      Subquery&lt;Timestamp&gt; sq = cq.subquery(Timestamp.class);
      Root&lt;GpsCoord&gt; gpsCoord = sq.from(GpsCoord.class);
  
      sq.select(criteriaBuilder.greatest(gpsCoord.get(GpsCoord_.generatedTs)));
      sq.groupBy(gpsCoord.get(GpsCoord_.device).get(Device_.deviceI));
      select.where(j.get(GpsCoord_.generatedTs).in(sq));
      
      
      TypedQuery&lt;Device&gt; query = this.getEntityManager().createQuery(cq);
  </code></pre>
  <p>Query that is generated is missing the GpsCoord fileds from the Join oepration</p>
  <pre><code>SELECT DISTINCT t0.DEVICE_I, t0.ACTIVE_S, t0.APP_CONFIG_I, t0.PREV_APP_CONFIG_I,     
  t0.APP_CONFIG_CONFIRMED_S, t0.APP_CONFIG_RECEIPT_D, t0.APP_CONFIG_SENT_S, 
  t0.CHANNEL_X,     t0.CREATION_TS, t0.CREATION_USER_I, t0.DEST_QUEUE_C, t0.DIVISION_C, 
  t0.LAN_IP_X, t0.LAST_UPDATE_TS, t0.LAST_UPDATE_USER_I, t0.MOBILE_IP_X, t0.ORIGIN_MP_I, 
  t0.PREV_CHANNEL_X, t0.TELEPHONE_NUMBER_X, t0.VERSION_X 
  
  FROM SPW_OWN.DEVICE t0, SPW_OWN.GPS_COORD t1 
  
  WHERE (t1.GENERATED_TS IN (SELECT MAX(t2.GENERATED_TS) FROM SPW_OWN.GPS_COORD t2, 
  SPW_OWN.DEVICE t3 WHERE t2.DEVICE_I = t3.DEVICE_I GROUP BY t3.DEVICE_I)) 
  
  AND t0.DEVICE_I = t1.DEVICE_I(+)
  </code></pre>
  <p>Using a multiselect from the CriteriaQuery like this generates the proper SQL when executed, but the following exception</p>
  <pre><code>Caused by: java.lang.Exception: java.lang.RuntimeException: Can not find constructor for &quot;class Device&quot; with argument types &quot;[class java.lang.String ... java.sql.Timestamp]&quot; to fill data.   
  </code></pre>
  <p>gets thrown after the call:</p>
  <pre><code>CriteriaQuery&lt;Device&gt; select = cq.multiselect(
                      //&quot;deviceI&quot;,
                      device.get(Device_.deviceI),
                      //&quot;activeS&quot;,
                      device.get(Device_.activeS),
                      //&quot;appConfigConfirmedS&quot;,
                      device.get(Device_.appConfigConfirmedS),
                      //&quot;appConfigReceiptD&quot;,
                      device.get(Device_.appConfigReceiptD),
                      //&quot;appConfigSentS&quot;,
                      device.get(Device_.appConfigSentS),
                      //&quot;channelX&quot;,
                      device.get(Device_.channelX),
                      //&quot;destQueueC&quot;,
                      device.get(Device_.destQueueC),
                      //&quot;lanIpX&quot;,
                      device.get(Device_.lanIpX),
                      //&quot;mobileIpX&quot;,
                      device.get(Device_.mobileIpX),
                      //&quot;prevChannelX&quot;,
                      device.get(Device_.prevChannelX),
                      //&quot;telephoneNumberX&quot;,
                      device.get(Device_.telephoneNumberX),
                      //&quot;versionX&quot;
                      device.get(Device_.versionX),
                      //&quot;device&quot;,
                      j.get(GpsCoord_.device),
                      //&quot;gpggaX&quot;,
                      j.get(GpsCoord_.gpggaX),
                      //&quot;gprmcX&quot;,
                      j.get(GpsCoord_.gprmcX),
                      //&quot;gpgsaX&quot;,
                      j.get(GpsCoord_.gpgsaX),
                      //&quot;gpgsvX&quot;,
                      j.get(GpsCoord_.gpgsvX),
                      //&quot;gpvtgX&quot;
                      j.get(GpsCoord_.gpvtgX),
                      j.get(GpsCoord_.generatedTs)
                      );
  </code></pre>