Clause 'where' with nested join not working using hql query

Question

I have 3 enteties:

@Data
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "event")
@FieldDefaults(level = AccessLevel.PRIVATE)
public class Event {

    @Id
    @GeneratedValue
    @Column(name = "id")
    UUID id;

    @Column(name = "name", nullable = false, unique = true)
    String name;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "event_id")
    Set<Session> sessions;
}

@Data
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "session")
@FieldDefaults(level = AccessLevel.PRIVATE)
public class Session {
    @Id
    @GeneratedValue
    @Column(name = "id")
    UUID id;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "session_id")
    Set<SessionHistory> sessionHistory;
}

@Data
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "session_history")
@FieldDefaults(level = AccessLevel.PRIVATE)
public class SessionHistory {

    @Id
    @GeneratedValue
    @Column(name = "id")
    UUID id;

    @Column(name = "status", nullable = false)
    @Enumerated(EnumType.STRING)
    Status status;

}

Event Repository:

@Repository
public interface EventRepo extends JpaRepository<Event, UUID> {
    Optional<Event> findByExternalId(Long externalId);

    @Query("SELECT e FROM Event e " +
            "JOIN e.sessions s " +
            "JOIN s.sessionHistory sh WHERE sh.status = 'SALE'")
    List<Event> findAllBy();
}

So I want to fetch session histories with only SALE state. Tests:

        SessionHistory sessionHistory1 = SessionHistory.builder()
                .status(Status.SOON)
                .build();

        SessionHistory sessionHistory2 = SessionHistory.builder()
                .status(Status.SALE)
                .build();

        Session session = Session.builder()
                .sessionHistory(Sets.newSet(sessionHistory1, sessionHistory2))
                .build();

        Event event = Event.builder()
                .name("SOMENAME")
                .sessions(Collections.singleton(session))

        eventRepo.save(event);

        Optional<Event> event = eventRepo.findAllBy().stream().findAny();

        assertEquals(1, eventByExternalId.get().getSessions()
                            .stream().findAny().get().getSessionHistory().size());

Select query:

    ...
    from
        event event0_ 
    inner join
        session sessions1_ 
            on event0_.id=sessions1_.event_id 
    inner join
        session_history sessionhis2_ 
            on sessions1_.id=sessionhis2_.session_id 
    where
        sessionhis2_.status='SALE'

But test fails with output:

org.opentest4j.AssertionFailedError: 
Expected :1
Actual   :2

What's the problem? If we try to use "where" for Session or Event in query it works fine. Also have tried join fetch.I didn't find solution with OneToMany associations. Is it even possible? I don't want to write native query. If you have some sophisticated approach of this issue it will be fine (like EntityGraph)

score 0 · Answer 1 · answered Mar 19 '21 at 19:21

0

You are getting the same entity twice because of the joins.

Add distinct

 @Query("SELECT distinct e FROM Event e " +
        "JOIN e.sessions s " +
        "JOIN s.sessionHistory sh WHERE sh.status = 'SALE'")
List<Event> findAllBy();

answered Mar 19 '21 at 19:21

Simon Martinelli

34,053
5
48
82

Unfortunately, no. I am getting different entities – Matvey Kukharchuk Mar 19 '21 at 20:48

score 0 · Answer 2 · answered Mar 23 '21 at 08:01

Your join is just a "random join" that doesn't affect the contents of the session history association. If you want to fetch just that, and I don't recommend this (follow the link for further explanation), you can use this query:

@Query("SELECT e FROM Event e " +
        "JOIN FETCH e.sessions s " +
        "JOIN FETCH s.sessionHistory sh WHERE sh.status = 'SALE'")
List<Event> findAllBy();

For a more detailed answer and an alternative solution based on DTOs take a look at this answer: Return ResultSet with intact joins in Hibernate / JPA

Clause 'where' with nested join not working using hql query

2 Answers2