How to query a given parameter with nested value inside a Set (or Collection)?

Viewed 95

I have an entity Foo with an embedded collection of Bars.

@Entity
@Table(name = "foo")
public class Foo {

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "bar", joinColumns = @JoinColumn(name = "foo_id"))
    private Set<Bar> bars = new HashSet<>();
    
    //...
}

@Embeddable
public class Bar {

    @Column(name = "value", nullable = false)
    private String value;

    //...
}

I'd like to find all Foos that match a given parameter (String) inside of the Set.

Some pseudo-code of what I want to achieve in JPQL:

Foo find(String toMatch) {
    return allFoos.stream()
            .filter(f -> f.getBars.stream()
                    .map(Bar::getValue)
                    .anyMatch(value -> value.equals(toMatch)))
            .findFirst()
            .get();
}

However when I try this query:

@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
    @Query("select f from Foo f " +
            "where (" +
            "  :toMatch in f.bars.value " +
            ")")
    Optional<Foo> find(@Param("toMatch") String toMatch);
}

It throws:

Caused by: org.hibernate.QueryException: illegal attempt to dereference collection [foo.id.bars] with element property reference [value]

I'd like to avoid a solution where I need to join multiple tables. This is pseudo code, but the actual query is filled with tons of big 'ol complex enterprise SQL where or and statements. So the less joins the better.

1 Answers

I do believe the correct JPQL query should look like:

select f from Foo f where f.id in (
 select b.id from f.bars b where b.value=:toMatch
)
Related