CODEWITHSUNDEEP
hibernatespring-data-jpahibernate-criteriacriteria-api
pixelstuermer
pixelstuermer

Reputation: 329

JOIN subquery using Spring Data JPA Specification

After a seemingly endless research I finally came up to the point where I see no hope, but asking here.

I am trying to transform a SQL query that joins to a subquery to a Spring Data JPA Specification. The reason for the JOIN is to get the latest timestamp grouped by foo. The used technologies are SpringDataJPA, Hibernate and a PostgreSQL database.

The Java entity and the plain SQL query are as follows (omitting constructors, getters, setters and other fields).

public class MyClass {

    OffsetDateTime timestamp;
    Foo foo;

    // Some more fields

}

SELECT *
FROM myclass
         JOIN(SELECT MAX(timestamp) AS timestamp
              FROM myclass
              GROUP BY foo) AS in1 ON myclass.timestamp = in1.timestamp;

I already have the following implementation, which produces the following stacktrace. Additionally, I don't quite know what to return here?

return new Specification<MyClass>() {
    @Override
    public Predicate toPredicate(Root<MyClass> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        CriteriaQuery<MyClass> query = criteriaBuilder.createQuery(MyClass.class);
        Root<MyClass> root = query.from(MyClass.class);
        Join<MyClass, MyClass> join = root.join("timestamp");
        join.on(criteriaBuilder.equal(join.get("timestamp"), root.get("timestamp")));

        return // ... what actually?
    }
};

org.hibernate.query.criteria.internal.BasicPathUsageException: Cannot join to attribute of basic type

Edit: Next to the JOIN, the following 2 alternatives would also work. Nevertheless, I still don't know how to represent them as a Criteria/Predicate.

Alternative 1: Subselect with multiple columns.

SELECT *
FROM myclass
WHERE (timestamp, foo) IN (SELECT MAX(timestamp) AS timestamp, foo
                                   FROM myclass
                                   GROUP BY foo);

Alternative 2: Select with multible tables on FROM.

SELECT *
FROM myclass,
     (SELECT MAX(timestamp) AS timestamp, foo
      FROM myclass
      GROUP BY foo) AS in1
WHERE myclass.timestamp = in1.timestamp
  AND myclass.foo = in1.foo;

Upvotes: 1

Views: 11615

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16410

Subqueries in the FROM clause are not supported by JPA nor Hibernate (Hibernate is working on this though), so if you want to stick with the standard you have only one option, which is not 100% correct as it will produce ties i.e. if multiple objects have the same max timestamp, you will see all of them:

SELECT e FROM myclass e WHERE EXISTS (
    SELECT 1
    FROM myclass e2
    GROUP BY e2.foo
    HAVING e.foo = e2.foo AND e.timestamp = MAX(e2.timestamp)
)

The exists predicate can be modeled with JPA Criteria like this:

return new Specification<MyClass>() {
    @Override
    public Predicate toPredicate(Root<MyClass> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        Subquery<Integer> subquery = query.subquery(Integer.class);
        subquery.select(criteriaBuilder.literal(1));
        Root<MyClass> subRoot = subquery.from(MyClass.class);
        subquery.groupBy(subRoot.get("foo"));
        subquery.having(
            criteriaBuilder.and(
                criteriaBuilder.equal(
                    root.get("timestamp"), 
                    criteriaBuilder.max(subRoot.get("timestamp"))
                ),
                criteriaBuilder.equal(
                    root.get("foo"), 
                    subRoot.get("foo")
                )
            )
        );

        return criteriaBuilder.exists(subquery);
    }
};

If you want to exclude ties, you will need support for limiting results which is not supported for subqueries in JPA and Hibernate yet. You could use Blaze-Persistence for this purpose though which supports that and many other advanced SQL features on top of JPA/Hibernate.

Upvotes: 2

Related Questions