Hibernate @Where soft delete on entity causing issues with JPA joins after upgrade to spring boot 3-CodePudding

I run into issue. I run spring boot 3. It worked in spring boot 2 I have such a relationship(simpliefied for case of question)

Group has users. Users have tokens.

@Entity(name = "Group")
@Where(clause = "not is_deleted or is_deleted is null")
@SQLDelete(sql = "UPDATE group SET is_deleted = true, updated_at = NOW() WHERE id=?")
class GroupEntity(
@OneToMany(mappedBy = "group", cascade = [CascadeType.ALL], fetch = FetchType.EAGER)
val users: MutableSet<UserEntity> = HashSet()

@Entity(name = "User")
@Where(clause = "not is_deleted or is_deleted is null")
@SQLDelete(sql = "UPDATE user SET is_deleted = true, updated_at = NOW() WHERE id=?")
class UserEntity(
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "group_id", nullable = true)
val group: GroupEntity,
@OneToOne(cascade = [CascadeType.ALL], mappedBy = "user", fetch = FetchType.EAGER)
var token: TokenEntity?

@Entity(name = "Token")
@Where(clause = "not is_deleted or is_deleted is null")
@SQLDelete(sql = "UPDATE token SET is_deleted = true, updated_at = NOW() WHERE id=?")
class TokenEntity(
@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "user_id", nullable = true)
val user: UserEntity

Now i have a test that checks if i can remove the user. As part of this test i want to fetch a grup that only has a removed user using standard JpaRepository.findById . However this always gives result:

NotFoundException(detail=Group with id 9aee9ef0-ac33-11ec-b909-0242ac120002 not found, throwable=null)

even though it does exist in test db. I have enabled hibernate sql debug option and found out the resulting query is this:

select * from group f1_0
                  left join user v1_0 on f1_0.id=v1_0.group_id
                  left join token t1_0 on v1_0.id=t1_0.user_id where
                   (not v1_0.is_deleted or v1_0.is_deleted is null) 
                    and f1_0.id='9aee9ef0-ac33-11ec-b909-0242ac120002' 
                    and (not f1_0.is_deleted or f1_0.is_deleted is null)

I expect a result where i get a grup entity with empty users set(as they are all deleted). However this line -

(not v1_0.is_deleted or v1_0.is_deleted is null)

being placed into a where clause gives a totally different behaviour - it will return nothing if all users inside the group are deleted. What i expect and want is this query:

select * from group f1_0
                  left join user v1_0 on f1_0.id=v1_0.group_id and
                  (not v1_0.is_deleted or v1_0.is_deleted is null)
                  left join token t1_0 on v1_0.id=t1_0.user_id where
                    f1_0.id='9aee9ef0-ac33-11ec-b909-0242ac120002' 
                    and (not f1_0.is_deleted or f1_0.is_deleted is null)

with the is deleted part of user moved into a join clause. I just have no idea how to get hibernate to do that

CodePudding user response：

After posting i tried one other thing: Adding @Fetch(FetchMode.SELECT) to collection of Users makes it work, however im still not happy with that solution really, im worried that this does not work as i expected out of the box and that i might have similar problems in other places/cases i might not have tests for

CodePudding user response：

Take a look at section 4 of the following article: https://www.baeldung.com/spring-jpa-soft-delete. It tells you not to use @Where and use @SQLDelete, @FilterDef, and @Filter if you want to query soft deleted data. You then apply the filter to the entity manager's session prior to using the repository. Remember to disable the filter when done.