CriteriaQuery subquery with max

Question

I have three classes like these:

Class A { 
    Long id;
    Date d;
    String e;
}

Class B { 
    Long id;
}

Class AB {
    Long aId;
    Long bId;
}

And I havet o build a CriteriaQuery to achieve the following:

select distinct(b.id) from b b
inner join ab ab on ab.bid = b.id
inner join a a on a.id = ab.aid
where e = 'SOMETHING'
and a.d in (
    select max(aa.d)
    from a aa
    inner join ab aabb on aa.id = aabb.aid
    inner join b bb on bb.id = aabb.bid
    where bb.id = b.id
)

But I am missing something because with:

List<Predicate> predicates = new ArrayList<Predicate>();
Join<B, A> joinAB = root.join("a", JoinType.INNER); // root is Root<B>
Subquery<A> sqA = query.subquery(A.class); // query is CriteriaQuery
Root<A> rootSqA = sqA.from(A.class);

sqA.select(builder.max((Expression)rootSqA.get("d")));
sqA.where(builder.and(joinAB.get("id").in(rootSqA.get("id))));                      
predicates.add(builder.in(sqA).value((Expression)joinAB.get("d")));

predicates.add(builder.equal(joinAB.get("e"), "SOMETHING"));

I am not getting what I want. I think what I am getting is all C objects (with their last A object) which have had an object A with e = "SOMETHING" at some point. But what I want is all C objects that have their last A object with e = "SOMETHING".

score 4 · Accepted Answer · answered Feb 06 '17 at 15:15

As too often happens, after posting a question here I realize I'm just dumb and I only have to fully understand the problem and most important the framework I'm using (I was new to CriteriaBuilder) and I come up with the answer to my own question.

So here it is:

Join<B, A> joinAB = root.join(B_.a, JoinType.INNER);
Subquery<Date> sqB = query.subquery(Date.class);
Root<B> rootSq = sqB.from(B.class);
Join<B, A> joinSq = rootSq.join(B_.lastA, JoinType.INNER);
sqB.select(builder.max((Expression)joinSq.get(A_.date))); 
sqB.where(builder.equal((Expression)rootSq.get(A.id), root.get(A_.id))); 
predicates.add(builder.and(
        builder.equal(joinAB.get(A_.d), sqB), 
        builder.equal(joinAB.get(A_.e), "SOMETHING")));

CriteriaQuery subquery with max

1 Answers1