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
falsethen extract all requests with emptyhotelBookingsand all requests where every booking have parameterjointCheckInis set tofalse - If a parameter is
truethen extract all requests that have one or more bookings withjointCheckInis 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))