Fetch @ElementCollection in one query with WHERE clause-CodePudding

Here is the main table:

@Entity
public class Group {

    @Id
    private Integer id;

    @ElementCollection(fetch = EAGER)
    @CollectionTable(name = "group_members",
            joinColumns = @JoinColumn(name = "group_id"))
    @Column(name = "email")
    private Set<String> members = new HashSet<>();

And the table which is ElementCollection in my case:

group_members {
int group_id,
varchar email
}

When I do a query with a join fetch, I get Group with all its Members in Set.

But, when I tried to filter it by one of the members, I get Group only with ONE member, which I searched.

Here is a query:

@Query(value = "select group from Group group left outer join fetch group.members members where members = :email")
List<Group> findByMember(@Param("email") String email);

The main goal is that it should be only one query, without an N 1 issue. Thanks in advance!

CodePudding user response：

The correct query, I believe, would be:

select distinct group from Group group 
 left join fetch group.members members 
 where :email member of group.members