JPA Specification predicate for querying latest/newest/most recent records

Question

I'm trying to convert the below query into a JPA specification in order to enable more flexible querying of my Rule entities, but i don't find any way of translating the below SQL query into a specification. I've been looking for possibilities of querying for "disctint on" with specs but i can't find any.

SELECT DISTINCT ON (name, key) * FROM (SELECT * FROM rules WHERE activated_at < NOW() AND name IN (?1) AND key IN (?2) ORDER BY activated_at DESC) AS tmp;

The above query gives me 1 rule per combination of name + key, with the most recent activated_at timestamps for each combination.

Some background:

A specific rule is identified with name + key
There can be multiple records with the same name + key, where the currently active rule is the one with the maximum activated_at timestamp, but is not a future value.

The IN clauses are straight forward with predicates like the below, but i can't find a way of querying for the most recent activated_at timestamp.

return (root, query, criteriaBuilder) -> root.get(key).in(keys);

Is this possible to achieve with JPA specifications?
Does anyone have any directions or finger pointers on how it can be achieved?

I'm not sure if JPA directly supports `distinct on` though you can always create a projection using your query with `@Query` annotation plus the native option set to true. Also why do you even need a subquery on that query ? — Jorge Campos, Apr 21 '22 at 07:13
See here for example: https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by the distinct on (name, key) doesn't work unless i first run the subquery to fetch and order the records from which i want to get distinct from. Currently that is being executed as a Query, however since i want to enable flexible querying in my API on 3 separate options (name, key and ruleIsActive) it seemed easier to use a spec instead of having to create 6 different methods in my repo interface to cover all possible query combinations. — tbjorch, Apr 21 '22 at 07:21
Why isn't this an aggregate query in the first place? `SELECT name, key, max(activated_at) FROM rules WHERE activated_at < NOW() AND name IN (?1) AND key IN (?2) GROUP BY name, key` — Turo, Apr 21 '22 at 07:42
@Turo i need the entire entity, not just the name, key and activated_at. The rule is used and linked to other objects in the business logic. — tbjorch, Apr 21 '22 at 07:49
Ok, then maybe with subqwuery? `SELECT * FROM rules r WHERE name IN (?1) AND key IN (?2) and activated_at = (SELECT max(avtivated_at) from rule r2 where r2.name = r.name and r2.key = r.key and activated_at < NOW())` — Turo, Apr 21 '22 at 07:58
@Turo yes that is what i need, but how can i achieve that subquery with a specification? I mean, currently i already achieve the same with the query in my question in a `@Query` annotation, but i want to transform it into a specification. — tbjorch, Apr 21 '22 at 08:07
So the whole thing is now boiled down to implement `subquery` in specification. right ? — Ratul Sharker, Apr 21 '22 at 08:15
@RatulSharker yes the query suggested by Turo returns the entities that I want to get — tbjorch, Apr 21 '22 at 08:17
Great !!! then the `subquery` it is. Let me update my answer — Ratul Sharker, Apr 21 '22 at 08:17

Ratul Sharker · Accepted Answer · 2022-04-21T08:33:09.977

Using the query @Toru suggested

SELECT * FROM rules r 
    WHERE 
    name IN (?1) 
    AND key IN (?2)
    AND activated_at = (
        SELECT max(avtivated_at) from rule r2
        where r2.name = r.name and r2.key = r.key
        and activated_at < NOW()
    )

In specification

public class Specs {
    public static Specification<Rules> getMaxActivatedRules(String name, String key) {

        return (root, query, builder) -> {

            // SubQuery portion start
            Subquery<Date> subQuery = query.subquery(Date.class);
            Root<Rules> subRoot = subQuery.from(Rules.class);

            Expression<Date> maxActivatedDateExpr = builder.max(subRoot.get(Rules_.activatedAt));
            
            Predicate subqueryNameEqual = builder.equal(subRoot.get(Rules_.name), name);
            Predicate subqueryKeyEqual = builder.equal(subRoot.get(Rules_.key), key);
            Predicate subqueryActivatedAtLessThenNow = builder.lt(root.get(Rules_.activatedAt), builder.literal("NOW()"));           
            subQuery.select(maxActivatedDateExpr).where(subqueryNameEqual, subqueryKeyEqual, subqueryActivatedAtLessThenNow);
            // Subquery portion end

            Predicate subQueryEqual = builder.equal(root.get(Rules_.activatedAt), subQuery);
            Predicate nameEqual = builder.equal(root.get(Rules_.name), name);
            Predicate keyEqual = builder.equal(root.get(Rules_.key), key);

            return builder.and(subQueryEqual, nameEqual, keyEqual );
        };
    }

}

I think your answer doesn't suite the current needs, see the comments... — Turo, Apr 21 '22 at 08:07
Thanks for taking the time @Ratul, but i need the entire entities, not a projection. I only make `select distinct on (name, key) *` which governs which entities to return, but i still get the entire objects. — tbjorch, Apr 21 '22 at 08:09

tbjorch · Answer 2 · 2022-04-21T09:28:08.153

With some modifications on @Ratuls answer, the below implementation was what i was looking for. The difference is that i don't want to base the subquery name and key on the input parameters, but let the subquery be based on the current root object's values.

private Specification<Rule> isActiveRule() {
    return (root, query, builder) -> {
      Subquery<Instant> subquery = query.subquery(Instant.class);
      Root<Rule> subRoot = subquery.from(Rule.class);

      Expression<Instant> maxActivatedAt = builder.greatest(subRoot.get(Rule_.activatedAt));

      Predicate subqueryNameEqual = builder.equal(subRoot.get(Rule_.name), root.get(Rule_.name));
      Predicate subqueryKeyEqual = builder.equal(subRoot.get(Rule_.key), root.get(Rule_.key));
      Predicate subQueryActivatedAtBeforeNow = builder.lessThan(subRoot.get(Rule_.activatedAt), Instant.now());
      subquery.select(maxActivatedAt).where(subqueryNameEqual, subqueryKeyEqual, subQueryActivatedAtBeforeNow);

      Predicate subQueryEqual = builder.equal(root.get(Rule_.activatedAt), subquery);

      return builder.and(subQueryEqual);
    };
  }

The neat part with this implementation is that is allows me to select which predicates to include in a spec inside my SpecificationBuilder class. Hence, if i don't want to supply the names or product keys, and just use the isActiveRule()predicate, i will get all rules that are active.

e.g.

var spec = RuleSpecificationBuilder.builder()
    .nameIn(names)
    .keyIn(keys)
    .isActiveRule()
    .build();

// OR

var spec = RuleSpecificationBuilder.builder()
    .isActiveRule()
    .build();

// OR

RuleSpecificationBuilder.builder()
    .nameIn(names)
    .isActiveRule()
    .build();

// etc..

The predicates nameIn(names), keyIn(keys) and isActiveRule() translates into this query which @Toru specified in a comment:

SELECT * FROM rules r WHERE name IN (?1) AND key IN (?2) and activated_at = (SELECT max(activated_at) from rule r2 where r2.name = r.name and r2.key = r.key and activated_at < NOW())

... where the isActiveRule() predicate translates into activated_at = (SELECT max(activated_at) from rule r2 where r2.name = r.name and r2.key = r.key and activated_at < NOW())

JPA Specification predicate for querying latest/newest/most recent records

Some background:

2 Answers2