@Query("SELECT new com.api.projection.GarageVehicleRequestLookupProjection" +
"(vr.id, vr.appUser.fullName, vr.appUser.unit.id, vr.appUser.unit.name, vr.createdAt, " +
"vr.startAt, vr.endAt, vr.destination,vr.reason, vr.assignedVehicle.id, " +
"vr.assignedVehicle.plate, vr.latestAction.vehicleRequestStatus,vr.appUser.unit.director.id, vr.appUser.unit.director.fullName,vr.driverRequested," +
"vr.garage.name, vr.vehicleTypeRequested, vr.requestType, vr.description, vr.assignedVehicleDriver.id, vr.assignedVehicleDriver.fullName)" +
" FROM VehicleRequest AS vr " +
"WHERE " +
"vr.appUser.fullName like %:fullName% " +
"AND (coalesce(:unitIds, null) is null or vr.appUser.unit.id in :unitIds) " +
"AND (coalesce(:garageIds, null) is null or vr.garage.id in :garageIds) " +
"AND (coalesce(:vehicleIds, null) is null or vr.assignedVehicle.id in :vehicleIds) " +
"AND (coalesce(:statuses, null) is null or vr.latestAction.vehicleRequestStatus in :statuses) " +
"AND (coalesce(:createdAt, null) is null or cast(:createdAt as date) = cast(vr.createdAt as date)) " +
"AND vr.startAt >= :startAt and vr.startAt <= :startAtEnd ")
Page<GarageVehicleRequestLookupProjection> getGarageVehicleRequestsWithFilter(
@Param("fullName") String fullName,
@Param("unitIds") List<Long> unitIds,
@Param("garageIds") List<Long> garageIds,
@Param("vehicleIds") List<Long> vehicleIds,
@Param("statuses") List<VehicleRequestStatus> status,
@Param("createdAt") Date createdAt,
@Param("startAt") Date startAt, @Param("startAtEnd") Date startAtEnd,
Pageable pageable);
There are 146 records in the VehicleRequest table.
When I run the hql statement above if vr.assignedVehicle is null then the record isn't being returned thus I get 36 records since only those records have an assignedVehicle(assignedVehicleId).
When I do it without projection like this,
SELECT vr FROM VehicleRequest AS vr ...
All of the records are being returned. The problem with the approach above is that there are too many joins therefore JPA makes too many requests to the database.
This is the statement generated with some of the unnecessary criteria removed,
select vehiclereq0_.id as col_0_0_,
appuser1_.full_name as col_1_0_,
appuser1_.unit_id as col_2_0_,
unit4_.name as col_3_0_,
vehiclereq0_.created_at as col_4_0_,
vehiclereq0_.start_at as col_5_0_,
vehiclereq0_.end_at as col_6_0_,
vehiclereq0_.destination as col_7_0_,
vehiclereq0_.reason as col_8_0_,
vehiclereq0_.assigned_vehicle_id as col_9_0_,
vehicle5_.plate as col_10_0_,
vehiclereq6_.vehicle_request_status as col_11_0_,
unit4_.director_id as col_12_0_,
appuser11_.full_name as col_13_0_,
vehiclereq0_.driver_requested as col_14_0_,
garage12_.name as col_15_0_,
vehiclereq0_.vehicle_type_requested as col_16_0_,
vehiclereq0_.request_type as col_17_0_,
vehiclereq0_.description as col_18_0_,
vehiclereq0_.assigned_vehicle_driver_id as col_19_0_,
appuser13_.full_name as col_20_0_
from vehicle_request vehiclereq0_
cross join app_user appuser1_
cross join unit unit4_
cross join app_user appuser11_
cross join vehicle vehicle5_
cross join vehicle_request_action vehiclereq6_
cross join garage garage12_
cross join app_user appuser13_
where vehiclereq0_.app_user_id = appuser1_.id
and appuser1_.unit_id = unit4_.id
and unit4_.director_id = appuser11_.id
and vehiclereq0_.latest_action_id = vehiclereq6_.id
and vehiclereq0_.garage = garage12_.id
and vehiclereq0_.assigned_vehicle_driver_id = appuser13_.id
and vehiclereq0_.assigned_vehicle_id = vehicle5_.id
and (coalesce(null, null) is null or appuser1_.unit_id in (null))
and (coalesce(null, null) is null or vehiclereq0_.garage in (null))
and (coalesce(null, null) is null or vehiclereq0_.assigned_vehicle_id in (null))
and (coalesce(null, null) is null or vehiclereq6_.vehicle_request_status in (null))
order by vehiclereq0_.created_at desc
These statements can be null,
and vehiclereq0_.assigned_vehicle_driver_id = appuser13_.id
and vehiclereq0_.assigned_vehicle_id = vehicle5_.id
In short, that's simply how HQL works. Inner join semantics are the default for path expressions. If you need left join semantics, you will have to write joins explicitly.
I think though, that this is a perfect use case for Blaze-Persistence Entity Views as this library on top of JPA/Hibernate has left join semantics for paths by default.
I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.
A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:
@EntityView(VehicleRequest.class)
public interface GarageVehicleRequestLookupProjection {
@IdMapping
Long getId();
AppUserProjection getAppUser();
@AttributeFilter(EqualFilter.class)
Instant getCreatedAt();
@AttributeFilter(BetweenFilter.class)
Instant getStartAt();
Instant getEndAt();
String getDestination();
String getReason();
VehicleProjection getAssignedVehicle();
VehicleDriverProjection getAssignedVehicleDriver();
@Mapping("garae.name")
String getGarageName();
VehicleType getVehicleTypeRequested();
RequestType getRequestType();
String getDescription();
boolean isDriverRequested();
@Mapping("latestAction.vehicleRequestStatus")
VehicleRequestStatus getStatus();
@EntityView(AppUser.class)
interface AppUserProjection {
String getFullName();
@Mapping("unit.id")
Long getUnitId();
@Mapping("unit.name")
Long getUnitName();
@Mapping("unit.director.id")
Long getUnitDirectorId();
@Mapping("unit.director.fullName")
Long getUnitDirectorFullName();
}
@EntityView(Vehicle.class)
interface VehicleProjection {
@IdMapping
Long getId();
String getPlate();
}
@EntityView(VehicleDriver.class)
interface VehicleDriverProjection {
@IdMapping
Long getId();
String getFullName();
}
}
Querying is a matter of applying the entity view to a query, the simplest being just a query by id.
GarageVehicleRequestLookupProjection a = entityViewManager.find(entityManager, GarageVehicleRequestLookupProjection.class, id);
The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features
Page<GarageVehicleRequestLookupProjection> findAll(Pageable pageable);
The best part is, it will only fetch the state that is actually necessary, similar to your custom query!
With Spring Data JPA Specifications or an EntityViewSettingProcessor you can also implement your filtering more efficiently: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features