Query JPA ElementCollection with List

Question

How do I query an @ElementCollection containing at least one element of my search criteria?

Given this corresponding database schema

for this entity class

@Entity
@Table(name = "companies")
public class Company {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @ElementCollection
    @CollectionTable(
        name="companies_products",
        joinColumns = @JoinColumn(name="company_id")
    )
    @Column(name="product")
    private List<String> products = new ArrayList<>();

    // ... getters/setters
}

I want to query the database for companies with a list of products. In SQL I can do

SELECT DISTINCT
    c.*
    FROM companies c
    LEFT JOIN companies_products cp ON c.id = cp.company_id
    WHERE
        cp.product IN ('food', 'electronics', 'books')

How whould an equivalent HQL/JPQL query look like?

I found Hibernate's collection-related expressions and qualified-path expressions but I was unable to create a working query.

• FROM Company c WHERE c.products IN (:prods) using List.of("food", "electronics", "books") as named parameter

  org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [phones] did not match expected type [java.util.Collection (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [food] did not match expected type [java.util.Collection (n/a)]

• FROM Company c WHERE value(c.products) IN (:prods)

  Could not create query for public abstract java.util.List com.example.demo.CompanyRepository.findWithProducts(java.util.List)! Reason: Validation failed for query for method public abstract java.util.List com.example.demo.CompanyRepository.findWithProducts(java.util.List)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.example.demo.CompanyRepository.findWithProducts(java.util.List)!

• FROM Company c WHERE elements(c.products) IN (:prods)

  Could not create query for public abstract java.util.List com.example.demo.CompanyRepository.findWithProducts(java.util.List)! Reason: Validation failed for query for method public abstract java.util.List com.example.demo.CompanyRepository.findWithProducts(java.util.List)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.example.demo.CompanyRepository.findWithProducts(java.util.List)!

Answer 1

Thanks Chris. Your suggested query Select c FROM Company c join c.products p WHERE p IN (:prods) works. Thanks for looking into it.