CODEWITHSUNDEEP
javasqljpamany-to-many
clemep
clemep

Reputation: 124

JPQL query for many to many relationship where item does not exist in join table

I'm trying to construct a JPQL query to select only VMs that do not exist in a Group. I have a many to many relationship between VMs and Groups:

class Group:

 @ManyToMany(fetch = FetchType.EAGER)
 @JoinTable(
     name="group_vm",
     joinColumns={@JoinColumn(name="group_id", referencedColumnName="id")},
     inverseJoinColumns={@JoinColumn(name="vm_id", referencedColumnName="id")}
 private Set<VM> vms;

This is fairly easy in SQL:

select * from vm where id not in (select vm_id from group_vm);

Is there a way to do this in JPQL?

Upvotes: 2

Views: 2119

Answers (1)

Multisync
Multisync

Reputation: 8797

Use NOT EXISTS

select vm from VM vm where not exists (select 1 from Group gr where vm member of gr.vms)

NOT IN should also work, but exists may be faster

select vm from VM vm where vm not in (select gr.vms from Group gr)

Upvotes: 3

Related Questions