CODEWITHSUNDEEP
javajpaspring-data-jpaspring-data-rest
Martin
Martin

Reputation: 3753

Unable to @Query for entities with a matching enum in an ElementCollection

I have an entity which has a list of enums, stored in a collection table as strings.

enum Color {
    RED,
    GREEN,
    BLUE
}

@Entity
class Product {

    @Id
    private Long id;

    @Column
    private String name;

    @ElementCollection
    @Enumerated(EnumType.STRING)
    @CollectionTable(name = "ProductColors", joinColumns = @JoinColumn(name = "product_id"))
    @Column(name="color")
    private List<Color> availableColors;
}

This works fine for creating, and fetching products through spring-rest-webmvc. But I'd like to be able to find all the products in a given color.

I tried the following:

@RepositoryRestResource
public interface ProductRepository extends CrudRepository<Long, Product> {
    @Query("select p from Product p where :color in p.availableColors")
    List<Product> findByColor(@Param("color") Color color);
}

But that gives an error:

... CROSS JOIN PRODUCTCOLORS COLORS1_ WHERE PRODUCT0_.ID = COLORS1_.PRODUCT_ID AND (? in (.[*])) ...
... expected "NOT, EXISTS, INTERSECTS, SELECT, FROM" ...

Is it possible to query against a simple ElementCollection like this, or do I have to move to a full-fledged entity to store the color relationship?

The answer at https://stackoverflow.com/a/34132646/153225 suggests it's possible to do it like this, but I can't see what I'm doing wrong.

Upvotes: 2

Views: 803

Answers (1)

Neil Stockton
Neil Stockton

Reputation: 11531

Quoting the JPQL BNF in the JPA spec.

in_expression ::=
{state_valued_path_expression | type_discriminator} [NOT] IN
{ ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }

in_item ::= literal | single_valued_input_parameter

In your case "p.availableColors" is not a "collection_valued_input_parameter" (or subquery, or the other option either). It is a field of your entity. Consequently that is not JPQL portable syntax you're trying.

However, using 

select p from Product p where :color MEMBER OF p.availableColors

ought to give what you need, and the JPA implementation will likely do an INNER JOIN to the enum join table, adding a WHERE clause on the particular enum member required.

Upvotes: 3

Related Questions