CODEWITHSUNDEEP
hibernatejpa-2.0projectionhibernate-criteriacriteria-api
verklixt
verklixt

Reputation: 703

JPA Criteria Subquery with select multiple colums and max() function

I need to write the following SQL code in Hibernate criteria query:

    SELECT
    a.*
FROM
    objekt a,
                --Filter on max version 
    (
        SELECT
            ueb_uuid,
            MAX(version) AS version
        FROM
            objekt
        GROUP BY
            ueb_uuid
    )              b
                        --concat tables
WHERE
        a.version = b.version
    AND a.ueb_uuid = b.ueb_uuid;

The only showstopper in this case is the subquery with selection of 2 columns and the max function

Here is what I tried

Subquery<Integer> subquery = query.subquery(Integer.class);
Root<ObjektEntity> subqueryRoot =   subquery.from(ObjektEntity.class);
    
subquery.select(builder.max(subqueryRoot.get(ObjektEntity_.version)));
    
subquery.where(builder.equal(root.get(AnotherEntity_.objekt),objekt.get(ObjektEntity_.uuid) )); 
     
query.where(builder.equal(root.get(ObjektEntity_.VERSION),subquery));

How can I create a subquery with a select on 2 columns and max funtion with 2 different return Values/Colums/Datatypes

Upvotes: 0

Views: 148

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16430

Prior to Hibernate 6.1 this was not possible to write, but your particular case could be rewritten to this form of query:

SELECT
    a.*
FROM
    objekt a
WHERE a.version = (select max(version) from objekt b where a.ueb_uuid = b.ueb_uuid)

which you can formulate with JPA Criteria.

Upvotes: 2

Related Questions