Make a query with sub condition for child list via Spring Data JPA
Tags: java, jpa, spring
Published: 2020-11-23 10:33:43

## Introduction

Recently I faced this situation when I needed to query an Entity with non-deleted children via Spring Data JPA and Hibernate.

Let's look at the domain model of my test application:

```java
@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private List<Item> items;

    ... getters, setters, equals, and hashcode.
}

@Entity
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "user")
    private User user;

    private Boolean deleted;
    
    ... getters, setters, equals, and hashcode.
}
```

The question was: how can I query all the users with non-deleted items?

The first idea was to use JPQL and `@Query` annotation inside the Spring Data `CrudRepository`. I wrote the next query:

```java
public interface UserRepository extends CrudRepository<User, Long> {

    @Query("from User u left join u.items i where i.deleted = false or i.deleted is null")
    List<User> findUserWithNonDeletedItems();

}
```

## Testing

The test code is:

```java
@SpringBootApplication
public class DemoApplication implements CommandLineRunner {

    @Autowired
    private UserService userService;

    @Autowired
    private UserRepository userRepository;

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        userService.createUser();
        userService.makeQuery();
    }
}

@Service
public class UserService {
    
    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Transactional
    public void makeQuery() {
        var result = userRepository.findUsersWithNonDeletedItems();
        assert result.get(0).getItems().size() == 1;
    }

    @Transactional
    public void createUser() {
        User userWithItems = new User();

        var items = List.of(
                new Item(userWithItems, false),
                new Item(userWithItems, true)
        );

        userWithItems.setItems(items);
        userRepository.save(userWithItems);

        User userWithoutItems = new User();
        userRepository.save(userWithoutItems);
    }
}
```

When I ran this code, I received `AssertionError`, because `findUsersWithNonDeletedItems()` returned 2 items for `userWithItems`, including a deleted item. The reason for this appearance is the absence of the `fetch` keyword.

Let's explain the difference between `left join` and `left join fetch` queries.

## Left join

If we make the next JPQL request:

```sql
from User u
left join u.items i
where i.deleted = false or i.deleted is null
```

Hibernate is going to generate the following SQL statement:

```sql
SELECT u.*
FROM user u
LEFT OUTER JOIN item i ON i.user_id = u.id
WHERE i.deleted = false OR i.deleted is null
```

It never requests items for every user. As result, it makes an additional query for receiving all user's items, which doesn't contain a deleted filter.

## Left join fetch

If we make the next JPQL request:

```sql
from User u
left join fetch u.items i
where i.deleted = false or i.deleted is null
```

Hibernate is going to generate the following SQL statement:

```sql
SELECT u.*, i.*
FROM user u
LEFT OUTER JOIN item i ON i.user_id = u.id
WHERE i.deleted = false OR i.deleted is null
```

In this query hibernate loads users with their items and filters items by deleted column. As result, we receive users with deleted items.

## Solution

Result query looks like this:

```java
public interface UserRepository extends CrudRepository<User, Long> {

    @Query("from User u left join fetch u.items i where i.deleted = false or i.deleted is null")
    List<User> findUsersWithNonDeletedItems();

}
```

It requests users with only non deleted items.