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?

Answer 1

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 );
        };
    }

}

Answer 2

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())