spring-data Specification and joined subquery

Question

I have the following Named query on my spring-data repository:

@Query("FROM Pedido p JOIN FETCH p.status ps WHERE ps.status IN (?1) AND ps.id IN (SELECT MAX(ps2.id) FROM PedidoStatus ps2 GROUP BY ps2.pedido)")

I'm trying to achieve the same result using the Criteria API and spring-data Specifications, this is what I have so far:

public static Specification<Pedido> byUltimoStatus(final List<PedidoStatus.StatusPedido> ultimoStatus) {
return new Specification<Pedido>() {
    public Predicate toPredicate(Root<Pedido> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

        Expression<PedidoStatus.StatusPedido> status = root.join("status").get("status");
        Predicate predicateByStatus = status.in(ultimoStatus);

        final Subquery<Long> subQuery = query.subquery(Long.class);
        final Root<PedidoStatus> ps = subQuery.from(PedidoStatus.class);

        Expression<Long> psId= ps.get("id");
        Expression<Long> maxId = builder.max(psId);

        subQuery.select(maxId);
        subQuery.groupBy(ps.get("pedido").get("id"));

        Predicate predicateByUltimoStatus = builder.in(root.join("status").get("id")).value(subQuery);

        return builder.and(predicateByStatus, predicateByUltimoStatus);             
    }
};}

It's still not working, looks like there is an extra INNERJOIN PedidoStatus in the result query.

This is the result of the @Query:

select ... from Pedido pedido0_ inner join PedidoStatus status1_ on pedido0_.id=status1_.pedido where (status1_.status in (? , ?)) and (status1_.id in (select max(pedidostat2_.id) from PedidoStatus pedidostat2_ group by pedidostat2_.pedido))

And this is the result of the Criteria API:

select ... from Pedido pedido0_ inner join PedidoStatus status1_ on pedido0_.id=status1_.pedido inner join PedidoStatus status2_ on pedido0_.id=status2_.pedido where (pedido0_.id is not null) and status1_.status IN (?, ?) and (status2_.id in (select max(pedidostat3_.id) from PedidoStatus pedidostat3_ group by pedidostat3_.pedido))

Did you found the solution? – Ruslan Stelmachenko Nov 19 '15 at 03:25 — Ruslan Stelmachenko, Nov 19 '15 at 03:25

score 0 · Answer 1 · answered Nov 01 '17 at 11:06

Knowing that this is a very old question, it looks to me like the reason for the duplicate INNERJOIN in the query generated by a CriteriaQuery is that the code building the query, does actually invoke root.join("status") twice. The result of the first invocation should be saved into a local variable, so you can reuse it, instead of joining twice.

First you do:

Expression<PedidoStatus.StatusPedido> status = root.join("status").get("status");

And later you do:

Predicate predicateByUltimoStatus = builder.in(root.join("status").get("id")).value(subQuery);

spring-data Specification and joined subquery

1 Answers1