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?
CodePudding user response:
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 );
};
}
}
CodePudding user response:
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())