title: Hibernate DetachedCriteria in FROM clause
body: <p>I have 2 tables:</p>
  
  <pre><code>    orders: id
      items: id, orderId, total, flag
  </code></pre>
  
  <p>I would like to make following query using Hibernate Criteria (DetachedCriteria):</p>
  
  <pre><code>    SELECT
          o.id,
          SUM(i1.total),
          SUM(i2.total)
      FROM
          orders o
              LEFT JOIN
              (
                  SELECT
                      i.orderId as orderId,
                      SUM(i.total) as total
                  FROM
                      items i
                 WHERE
                      i.flag = 0
                 GROUP BY
                      orderId
             ) AS i1
             ON i1.orderId = o.id
              LEFT JOIN
             (
                 SELECT
                  i.orderId as orderId,
                    SUM(i.total) as total
                  FROM
                     items i
                 WHERE
                     i.flag = 1
                 GROUP BY
                      orderId
              ) AS i2
              ON i2.orderId = o.id
      GROUP BY
          o.id
  </code></pre>
  
  <p>I know how to use <code>DetachedCriteri</code>a to create subquery in <code>WHERE</code> clause, but as you can see, I need to do a subquery in <code>FROM</code> clause. If it is not possible, maybe there is a way to write it in <code>SELECT</code> clause (inside <code>SUM()</code>), because this query could be rewritten to such form.</p>
  
  <p>I really need to use Criteria API even if I have to pass native SQL to the query.</p>
  
  <p>I didn't show you classes or mapping, but as you can see, this is a very simple example.</p>