CODEWITHSUNDEEP
jpacriteria-api
Jonas
Jonas

Reputation: 443

JPA Critiera query with many joins using three OR predicates

I'm writing a query using JPA Criteria API where I want to get all travels matching any of these predicates:

The first predicate is easy. I just check if owner = user in MasterTravel. For the second predicate I need to join MasterTravel->MTT->Travel->Passenger and then check if Passenger_.user = user. For the third predicate I need to join in another direction MasterTravel->MasterTravelApplication and check for MasterTravelApplication_.user = user. This is what breaks my query since I've got the two first working but when I try to add the last predicate it stops working.

Should I make a new root for the last join, or how can I take the union of Joins 1 and Joins 2 below?

CriteriaQuery<MasterTravel> q = cb.createQuery(MasterTravel.class);
Root<MasterTravel> root = q.from(MasterTravel.class);

// Joins 1, these works fine
Join<MasterTravel, MTT> mtts = root.join(MasterTravel_.mtt);
Join<MTT, Travel> travels = mtts.join(MTT_.travel);
Join<Travel, Passenger> passengers = travels.join(Travel_.passengers);                      

// Joins 2, this doesn't work
Join<MasterTravel, MasterTravelApplication> application = root.join(MasterTravel_.applications);

Predicate p = cb.or(
    cb.equal(passengers.get(Passenger_.user), user), 
    cb.equal(root.get(MasterTravel_.owner), user),
    cb.equal(application.get(MasterTravelApplication_.user), user));  // When I add this it stops working

q.select(root);
q.where(p);         
q.orderBy(cb.desc(root.get(MasterTravel_.createdTimestamp)));                       
q.distinct(true);

List<MasterTravel> resultList = em.createQuery(q).getResultList();

Upvotes: 2

Views: 4505

Answers (1)

JB Nizet
JB Nizet

Reputation: 691755

You don't describe the links between entities, but I would guess that a master travel doesn't necessarily have at least one application, and since you're doing an inner join, all the master travels that don't have any application are not found.

Use the join method taking a JoinType as argument, and use a JoinType.LEFT.

Upvotes: 2

Related Questions