Hibernate left join fetch performing additional query for subset

Question

I would like to query for Roles with Assignments which has date_to equal null in one query. I need all roles returned even those without assigned assignments.

Relation in Role entity is set:

 @OneToMany(mappedBy = "role") private List<Assignment> assignment;

Assignment Entity has relation to Permission:

@ManyToMany @JoinTable( name = "assignment_version_permission", joinColumns = @JoinColumn(name = "assignment_version_id"), inverseJoinColumns = @JoinColumn(name = "permission_id")) private Set<Permission> permissions; @Column(name = "date_to") private LocalDateTime dateTo;

and from Permission entity:

@ManyToMany @JoinTable( name = "assignment_version_permission", joinColumns = @JoinColumn(name = "permission_id"), inverseJoinColumns = @JoinColumn(name = "assignment_version_id")) @ToString.Exclude private List<Assignment> assignments;

I tried to query for all roles and their assignments ONLY where date_to equal null.

@Query("from Role r left join Assignment av on r.dbid = av.role.dbid and av.dateTo is null left join av.permissions p where r.name in :names") List<Role> readByRoleNameInOnlyActiveAssignments (List<String> names);

The above query works, but after role.getAssignemts () I would only like to receive assignments where date_to is null or null if there are no assignments.

Now role.getAssignemts() does an additional query for all Assignments related to Role. When a role has no active Assignments, I don't want to receive any of them. How can I achieve it?

I tried with EntityGraph but I there is no option to add contition on assignemns (return only these where assignment date_to is null)

@EntityGraph(attributePaths = {"assignments", "assignments.permissions"}) List<Role> readByNameIn(List<String> names);

Christian Beikov · Accepted Answer · 2021-10-19 09:43:31Z

That's not directly possible because fetching only a subset of a collection is unsafe. If you alter the collection, elements that are filtered out would be deleted in a collection recreate event, which is why applying ON conditions is disallowed for fetch joins. The second query you are seeing is because Hibernate does not know the Assignment you joined correspond to the assignments association.

The only way to implement this is to use a DTO approach and I think this is a perfect use case for Blaze-Persistence Entity Views.

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(Role.class) public interface RoleDto { @IdMapping Long getId(); String getName(); @Mapping("assignments[dateTo IS NULL]") Set<AssignmentDto> getAssignments(); @EntityView(Assignment.class) interface AssignmentDto { @IdMapping Long getId(); Set<PermissionDto> getPermissions(); } @EntityView(Permission.class) interface PermissionDto { @IdMapping Long getId(); String getAction(); } }

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

RoleDto a = entityViewManager.find(entityManager, RoleDto.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<RoleDto> findAll(Pageable pageable);

or in your case

List<RoleDto> findAllByNameIn(List<String> names);

The best part is, it will only fetch the state that is actually necessary!

Collectives™ on Stack Overflow

Hibernate left join fetch performing additional query for subset

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related