CODEWITHSUNDEEP
jpamany-to-manyjpql
Pablo Ignacio
Pablo Ignacio

Reputation: 21

JPQL query: Item that belongs to multiple categories

Having an entity Items that have a collection field called categories, I need to find items that belong at least to two specific categories, but I cant find the way to build the correct JPQL query.

@Entity
public class Items {
@ManyToMany (fetch = FetchType.EAGER)
private List<Category> categories;
}

I can find an item that have one category:

SELECT i FROM Item i WHERE :cat MEMBER OF item.categories

I can select items with any of several categories:

SELECT i FROM Item i WHERE :cat1 MEMBER OF item.categories OR :cat MEMBER OF item.categories

But when I try to select for items that have at least two specific categories, the following query gets no items:

SELECT i FROM Item i WHERE :cat1 MEMBER OF item.categories AND :cat2 MEMBER OF item.categories

Which is the correct way to do that?

Best regards, Pablo.

Upvotes: 1

Views: 83

Answers (1)

Pablo Ignacio
Pablo Ignacio

Reputation: 21

That problem happened to me with ObjectDB. I have contacted them too, and this was the answer:

This seems to be the result of how JPQL queries are converted into SQL like syntax before executing. MEMBER OF is implemented using JOIN with a new synthetic variable for iteration over the collection. In your query item.categories appears twice, but the same synthetic variable for iterating over that collection is used for both occurrences, and that variable cannot match both sides of the AND with the same value.

Possibly we may have to use separate iteration per MEMBER OF as the results as demonstrated by your post seem unacceptable (although JQPL itself has strange behaviour in some known cases due to the conversion to JOIN). However, using separate variables may sometimes make the query more complex and slow unnecessarily (e.g. for the OR query in your post), so any change requires careful planning.

As a quick solution, you may replace AND of MEMBER OF with 2 explicit JOIN variables for iteration over the collection with 2 independent variables.

So, the solution is to use the following query:

SELECT DISTINCT item
FROM Item item JOIN item.categories cat1 JOIN item.categories cat2
WHERE cat1 = :cat1 AND cat2 = :cat2

I don't know if this a problem only for this specific JPA implementation (ObjectDB). Anyway, if anybody has a similar problem, I hope that this post can help.

Upvotes: 1

Related Questions