Hibernate DetachedCriteria in FROM clause

Question

I have 2 tables:

    orders: id
    items: id, orderId, total, flag

I would like to make following query using Hibernate Criteria (DetachedCriteria):

    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

I know how to use DetachedCriteria to create subquery in WHERE clause, but as you can see, I need to do a subquery in FROM clause. If it is not possible, maybe there is a way to write it in SELECT clause (inside SUM()), because this query could be rewritten to such form.

I really need to use Criteria API even if I have to pass native SQL to the query.

I didn't show you classes or mapping, but as you can see, this is a very simple example.

Answer 1

I found solution for my problem. I had to make a POJO and mapping:

<class name="OrderTotal"
    entity-name="OrderTotalForFlag0">
    <subselect>
        SELECT
            i.orderId AS id,
            SUM(i.total) AS total
        FROM
            items i
        WHERE
            i.flag = 0
        GROUP BY
            id
    </subselect>
    <id name="id" type="int" />
    <property name="total" type="int" />
</class>

And of course for flag 1 it will be similar. This could be also done by declaring views in database and creating a mappings to that views. In this example I was using the same POJO class, but different entity-name.

Then I made a property in class Order and mapping:

<one-to-one name="orderTotalForFlag0" entity-name="OrderTotalForFlag0" />
<one-to-one name="orderTotalForFlag1" entity-name="OrderTotalForFlag1" />

Of course laziness could be set.

I'm pretty sure now, that this could be also done in different way - by making subquery in SELECT clause - using the formula attribute/element in mapping for properties, but this would work slower than subquery in FROM clause.

And of course all of that was in documentation to Hibernate ;)