I have two JPA-entities:
public class BusinessTripRequest extends StandardEntity { @OneToMany(mappedBy = "businessTripRequest", fetch = FetchType.LAZY) @OnDelete(DeletePolicy.CASCADE) @Composition protected List<HotelBooking> hotelBookings; } public class HotelBooking extends StandardEntity { @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "BUSINESS_TRIP_REQUEST_ID") protected BusinessTripRequest businessTripRequest; @Column(name = "JOINT_CHECK_IN") protected Boolean jointCheckIn; }
and I tried to write a JPQL query to extract requests that:
- If the parameter is
false
then extract all requests with emptyhotelBookings
and all requests where every booking have parameterjointCheckIn
is set tofalse
- If a parameter is
true
then extract all requests that have one or more bookings withjointCheckIn
is set totrue
I wrote something like this
select e from nk$BusinessTripRequest e join e.hotelBookings hb
where (true = ? and e.hotelBookings is not empty and hb.jointCheckIn = true)
or (false = ? and e.hotelBookings is empty)
It works well when parameter is true
because of the first condition. But I can’t write a working condition for false
parameter
Advertisement
Answer
solution suggested from comments
select e from nk$BusinessTripRequest e where (true = ? and e.id in (select hb1.businessTripRequest.id from HotelBooking hb1 where hb1.jointCheckIn = true)) or (false = ? and {E}.id not in (select hb1.businessTripRequest.id from nokia$HotelBooking hb1 where hb1.jointCheckIn = true))