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 ?
CodePudding user response:
This is in JPA, but maybe helps:
private EntityManager entityManager;
public void run(ApplicationArguments args) throws Exception {
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