How to query for all entries a WHERE "b MEMBER OF a.list"

Question

I have an entity, let's say A, which has a field that is a list of another entity, let's say B.

This is A:

@Entity
@Table(name = "a")
public class A implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    @ManyToMany
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @JoinTable(name = "a_b",
               joinColumns = @JoinColumn(name="as_id", referencedColumnName="id"),
               inverseJoinColumns = @JoinColumn(name="bs_id", referencedColumnName="id"))
    private Set<B> bs = new HashSet<>();
}

This is B:

@Entity
@Table(name = "b")
public class B implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

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

    @Column(name = "description")
    private String description;
}

Now, what I'm trying to achieve is to be able to query for all the entries a of A, having an entity b of B in a.bs

Tha actual query is show below:

@Repository
public interface ARepository extends JpaRepository<A, Long> {
    @Query("SELECT DISTINCT a FROM A a LEFT JOIN FETCH a.bs bs WHERE :b MEMBER OF bs")
    List<A> findAllByB(@Param("b") B b);
}

Wasn't it supposed to work like this?

However the error I get is the one below, I put it on pastebin due to exceeding the size limits of the post:

Log

Answer 1

I think you don't need join here.

Try this query:

@Query("SELECT DISTINCT a FROM A a WHERE :b MEMBER OF a.bs")

You're using MEMBER OF operator but match it with another table, not the collection itself so it leads to sql syntax error.

By default PostgreSQL create a public schema, if you don't specify schema for you entity, it will be not found when hibernate checking against public schema.

@Table(name = "b", schema = "schemaname")