[FIXED] JPA Criteria Query order by add addtional join

Issue

I have an issue with ordering with CriteriaBuilder, I have a complex model but will simplify it in this example : I have many associated entities :

A {long id, String name}
B {long id, String z, A a}
C {long id, String x, String y, B b}

I use CriteriaBuilder to create the query like this :

    CriteriaBuilder cb = session.getCriteriaBuilder();
    CriteriaQuery<C> criteria = cb.createQuery(C.class);
    Root<C> root = criteria.from(C.class);
    Join<Object, Object> joinPredicate = (Join<Object, Object>) root.fetch("b", JoinType.INNER);
    // Apply some filters on the Predicate
    joinPredicate = (Join<Object, Object>) joinPredicate.fetch("a", JoinType.INNER);
    // Apply some filters on the Predicate
    var finalPredicate = cb.and(predicates.toArray(Predicate[]::new));
    criteria.where(finalPredicate);
    criteria.select(root);

Query obtained is :

select
    c0_.x as xxx,
    c0_.y as yyy,
    b1_.z as zzz,
    a2_.name as name
from
    C c0_ 
inner join
    B b1_ 
        on c0_.bID=b1_.id 
inner join
    A a2_ 
        on b1_.aID=a2_.id 
where
    // some filters on A, B & C

Now I have a requirement to add an order by on A.name, so I added :

criteria.orderBy(cb.asc(root.get("b").get("a").get("name")));

The problem is that it's adding an additional join to the query instead of using the one already there !

select
    c0_.x as xxx,
    c0_.y as yyy,
    b1_.x as xxx,
    b1_.y as yyy,
from
    C c0_ 
inner join
    B b1_ 
        on c0_.bID=b1_.id 
inner join
    A a2_ 
        on b1_.aID=a2_.id 
join
    A a3_
where
    // some filters on A, B & C
order by
    a3_.name asc

Could you please help with the right way to order ?

Solution

This is in JPA, but maybe helps:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<C> criteria = cb.createQuery(C.class);
Root<C> root = criteria.from(C.class);
Join<C, B> joinB = root.join("b", JoinType.INNER);
Join<B, A> joinA = joinB.join("a", JoinType.INNER);
Path<String> aName = joinA.get("name");
criteria.where(cb.equal(root.get("x"), "X"), cb.equal(joinB.get("z"), "Z"), cb.equal(aName, "Name"));
criteria.select(root);
criteria.orderBy(cb.asc(aName));
TypedQuery<C> q = entityManager.createQuery(criteria);
q.getResultList();

gives:

select c0_.id as id1_2_, c0_.b_id as b_id4_2_, c0_.x as x2_2_, c0_.y as y3_2_ 
from c c0_ 
    inner join b b1_ on c0_.b_id=b1_.id 
    inner join a a2_ on b1_.a_id=a2_.id 
where c0_.x=? and b1_.z=? and a2_.name=? 
order by a2_.name asc   

Answered By - K.Nicholas
Answer Checked By - Marilyn (JavaFixing Volunteer)