Home > OS >  When relationship to a X column is null the records are not selected in the HQL statement. (PostgreS
When relationship to a X column is null the records are not selected in the HQL statement. (PostgreS

Time:08-17

@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

CodePudding user response:

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

  • Related