CODEWITHSUNDEEP
javasqljpanamed-query
RNJ
RNJ

Reputation: 15552

Checking if ID contained in a list in JPA

I have an entity which contains a list of other entities. For example it could be Person with a list of Address entities. The addresses are not specific to one person. (it is actually a many to many relationship with a join table in the middle in the DB)

I am doing a search to try and find all persons who do not have a an address with id X

(This might not make sense because of the triviality of the example. In my real world case it does :) )

By named query is:

SELECT p FROM Person p left join fetch p.addresses a WHERE p.addresses IS EMPTY OR a.id != :addressId

addressId is the id of the address that I pass in

My problem is best explained with an example

and now the desired results

Currently when I pass in address 1 I get all three back. I think this is because person 1 also has address 2 which means it gets through the query.

Does any one know how I can search for an id in the list and if it exist in any of the element then not return it?

Hope this makes sense. If not I can try and provide more details

Thanks

Upvotes: 3

Views: 3365

Answers (2)

Oleksandr Bondarenko
Oleksandr Bondarenko

Reputation: 2018

You could just use this query:

select p from Person p where not exists 
(select a from Person pe left join pe.addresses a where p = pe and a.id = ?1)

Upvotes: 5

RNJ
RNJ

Reputation: 15552

I think I have found a way to do this by using the ID to retrieve the actual entity. Then I can use the NOT MEMBER OF

For example instead of this

SELECT p FROM Person p left join fetch p.addresses a WHERE p.addresses IS EMPTY OR a.id != :addressId

I do 

entityManager.find(Address.class, addressId);
SELECT p FROM Person p WHERE :address NOT MEMBER OF p.addresses

I have to do the extra call for entityManger.find but I do not have to do the join explicitly in the JPQL. I am not sure if this is more inefficient because of this extra call. It does solve the problem though albeit with a slightly different approach.

I'd still be interested to see if it can be done using just the id though...

Upvotes: 2

Related Questions