Search code examples
javasqljpanamed-query

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

  • Person 1 may have address 1 and address 2
  • Person 2 may have address 2
  • Person 3 may have address 3

and now the desired results

  • if I pass in address 1 then I want person2 and person 3 returned
  • if I pass in address 3 then I want person1 and person 2 returned
  • if I pass in address 2 then I want person 3 returned

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

Solution

  • 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)