SQL Query Too Complex To Express In JPA Criteria API?
<p>I have an SQL query that gets me exactly the data I need. The problem is that we are trying to express all queries in JPA Criteria API to maintain portability, and I can't figure out how to map this particular query.</p>

<p>The problem is that the JPA Criteria API Subquery class lacks the  multiselect() method that CriteriaQuery class has. As you can see in the SQL query, I have computed fields in the sub-query which don't exist in the entity. Thus, I have no way to retrieve these fields.</p>

<p>I would be quite appreciative if anyone knows a solution or could offer guidance, or even if someone could validate that what I am trying to achieve in JPA Criteria API is not possible.</p>

<p>The SQL:</p>

<pre><code>SELECT w.NAME   AS 'wave_name', 
   Count(*) AS 'num_lines', 
   Sum(qty_ordered) AS 'num_units', 
   Count(DISTINCT unit_of_work_id) AS 'num_units_of_work', 
   Sum(completed_units) AS 'completed_units', 
   ( Sum(completed_units) + Sum(qty_scratched) ) / Sum(qty_ordered) AS 'perc_completed_units' 
FROM   (SELECT t.id, 
           t.wave_id, 
           t.quantity_requested  AS 'qty_ordered', 
           t.quantity_scratched    AS 'qty_scratched', 
           t.unit_of_work_id     AS 'unit_of_work_id', 
           Ifnull(m.quantity, 0) AS 'qty_picked', 
           CASE 
             WHEN Ifnull(m.quantity, 0) &gt; quantity_requested THEN 
             quantity_requested 
             ELSE Ifnull(m.quantity, 0) 
           END                   AS 'completed_units' 
    FROM   task t 
           LEFT OUTER JOIN (SELECT move.task_id, 
                                   Sum(quantity) AS 'quantity' 
                            FROM   move
                            GROUP  BY task_id) m 
                        ON m.task_id = t.id) s 
   JOIN wave w 
     ON w.id = s.wave_id 
GROUP  BY w.name;
</code></pre>

<p>The entities:</p>

<pre><code>@Entity
@Table(name = "task")
public class Task {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;

    @ManyToOne (cascade = CascadeType.ALL)
    @JoinColumn (name = "wave_id", nullable = false)
    private Wave wave;

    @ManyToOne (cascade = CascadeType.ALL)
    @JoinColumn (name = "unit_of_work_id", nullable = false)
    private UnitOfWork unitOfWork;

    @OneToMany (cascade = CascadeType.ALL, mappedBy = "task")
    private Set&lt;Move&gt; moves = new HashSet&lt;Move&gt;();

    @Column (name = "quantity_requested")
    private Long quantityRequested;

    @Column (name = "quantity_scratched")
    private Long quantityScratched;
}

@Entity
@Table(name = "wave")
public class Wave {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "wave", cascade = CascadeType.ALL)
    private Set&lt;Task&gt; tasks = new HashSet&lt;Task&gt;();
}

@Entity
@Table(name = "unit_of_work")
public class UnitOfWork {

    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    @Column (name = "id")
    private Long id;

    @OneToMany(mappedBy = "unitOfWork", cascade = CascadeType.ALL)
    private Set&lt;Task&gt; tasks = new HashSet&lt;Task&gt;();
}

@Entity
@Table(name = "move")
public class Move {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;

    @ManyToOne (cascade = CascadeType.ALL)
    @JoinColumn (name = "task_id", nullable = false)
    private Task task;

    @Column (name = "quantity")
    private Long quantity;
}
</code></pre>