I want hibernate to use my own join (left outer join t.route Operations routeOperation) in its internal query. And now it turns out that in order for me to remove the n+1 problem, I need to add routeOperations to the EntityGraph. But then he will create another join and break my logic. Who knows how to solve this?
My code:
@Query("SELECT t FROM AzsEntity t " +
"left outer join t.routeOperations routeOperation " +
"where t.company.id = :companyId " +
"and " +
"((routeOperation.timeEnd is not null and routeOperation.timeEnd > :timeStart and routeOperation.timeStart < :timeEnd) " +
"or (routeOperation.timeEnd is null and routeOperation.timeStart is null))")
@EntityGraph(attributePaths = {"reservoirs.fuelBrand.fuelType", "reservoirs.remains",
"reservoirs.hourlySells", "routeOperations"})
Page<AzsEntity> findAzsEntitiesByCompanyId(Pageable pageable, @Param("companyId") Long companyId,
@Param("timeStart") LocalDateTime timeStart,
@Param("timeEnd") LocalDateTime timeEnd);
and sql query from hibernate generated sql:
select // ** //
left outer join asdb.route_operation routeopera1_ on azsentity0_.id=routeopera1_.point_id
left outer join asdb.route_operation routeopera2_ on azsentity0_.id=routeopera2_.point_id
where azsentity0_.type='AZS'
and ( azsentity0_.status = 'ACTIVE') and azsentity0_.company_id=?
and ((routeopera1_.time_end is not null) and routeopera1_.time_end>? and routeopera1_.time_start<? or (routeopera1_.time_end is null)
and (routeopera1_.time_start is null)) order by azsentity0_.id desc
Hibernate generate not need 'routeopera2_'. I i remove routeOperations from entityGraph that i get much queries... thanks