Spring -data Specification and Combined Subquery

Question

I have the following Named query in 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 am trying to achieve the same result using the criteria and specifications API spring -data, 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 still does not work, it seems that there is an additional INNERJOIN PedidoStatus as a result of the request.

This is the result of @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 API criteria:

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

Answer 1

, , , INNERJOIN , CriteriaQuery, , , , root.join("status") . , , .

:

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

:

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