Make request with subcondition for child list via Spring Data JPA

Question

Assume I have the next data model:

@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.
}

I need to query a certain user by id with non-deleted items. Can I do it via Spring Data Repositories?

I tried something like:

public interface UserRepository extends CrudRepository<User, Long> {

    @Query("from User u left join u.items i on i.deleted = false where u.id = ?1")
    List<User> findUserWithNonDeletedItems(Long userId);
}

But this approach generates 2 separate SQL queries:

select user0_.id as id1_1_0_, items1_.id as id1_0_1_, items1_.deleted as deleted2_0_1_, items1_.user as user3_0_1_ from user user0_ left outer join item items1_ on user0_.id=items1_.user and (items1_.deleted=0) where user0_.id=?
select items0_.user as user3_0_0_, items0_.id as id1_0_0_, items0_.id as id1_0_1_, items0_.deleted as deleted2_0_1_, items0_.user as user3_0_1_ from item items0_ where items0_.user=?

And as result, I receive user with deleted items in the item list.

why do you join tables on `deleted` field, which has nothing in common with the foreign key? For HQL you don't need to specify it at all. `from User u left join u.items i where u.id = ?1` must be enough — Dmitrii Bocharov, Nov 22 '20 at 13:25
I need to join only items with deleted = false field. It's additional join condition. — Golov Pavel, Nov 22 '20 at 13:35

score 3 · Accepted Answer · answered Nov 22 '20 at 13:41

3

Nothing wrong with creation of two separete queries. One is to get users from user table, other is to get items of related user from items table.

join clause is used to combine columns from one or more tables. join u.items i on i.deleted = false is not a proper use. It should be on the where clause.

You should change the query this way:

@Query("from User u left join u.items i where i.deleted = false and u.id = ?1")
List<User> findUserWithNonDeletedItems(Long userId);

answered Nov 22 '20 at 13:41

Hülya

3,353
2
12
19

I tried this way, but unfortunately, when I call this method it returns User with deleted employees. I think it is the result of the second query. In this query hibernate select all items, connected with a certain user without ```deleted = false``` condition. – Golov Pavel Nov 22 '20 at 15:10
1

Can you try `@Query("from User u join fetch u.items i where i.deleted=false and u.id = ?1")` – Hülya Nov 22 '20 at 19:58
It works. But I can't understand, why the ```fetch``` keyword helped me? With ```fetch``` keyword hibernate makes only one SQL query. – Golov Pavel Nov 23 '20 at 08:04
1

https://stackoverflow.com/a/59468551/6789065 This answer gave me a full explanation. Thank you! – Golov Pavel Nov 23 '20 at 08:25

Make request with subcondition for child list via Spring Data JPA

1 Answers1