CODEWITHSUNDEEP
sqljpajpql
ZZ 5
ZZ 5

Reputation: 1964

JPQL Query - object that's in one list and isn't in another

I want to find list of entities that are present in the first list and reject entities if they exists in second list.
Example data:
I've got Products: P1,P2 and P3. Another (main) entity is Items: I1 and I2. I1 entity contains P1, and P2 and I2 contains P2 and P3. I want to find list of Items, if they're present in List1(contains just P1) and reject every Item if it's present in List2(contains just P3). So it should give me I1 as output.
My query and code:

Query query = this.em.createQuery("SELECT distinct(i) FROM Item i join fetch i.products ip WHERE ip.product IN :list1 AND ip.product NOT IN :list2");
    query.setParameter("list1", list1);
    query.setParameter("list2", list2);

This query gives me list of both Items. What's wrong here?

Upvotes: 0

Views: 960

Answers (1)

Predrag Maric
Predrag Maric

Reputation: 24433

Try this

SELECT distinct i FROM Item i join fetch i.products ip WHERE :list1 IN ip and :list2 NOT IN ip

UPDATE

I've been trying to get this to work myself, but with no results. @NeilStockton suggested it is not supported in JPA specification.

However, in your example you have parameter lists with one element. In case of single element parameters, this should work:

Query query = this.em.createQuery("SELECT distinct i FROM Item i join fetch i.products ip WHERE :param1 IN elements(ip) and :param2 NOT IN elements(ip)");

query.setParameter("param1", product1);
query.setParameter("param2", product2);

Upvotes: 1

Related Questions