JPA selects from many-to-many collection

Question

I got 2 entity classes with a many-to-many association

ModPm:

@Entity @Table(name="MOD_PM") public class ModPm extends WebPageObject implements Serializable, IDBNamedEntity { private static final long serialVersionUID = 1L; public final static String Q_GET_WITHOUT_STATUS_FOR_SCOPE = "ModPm.getWithoutStatusForScope"; @Id private long id; ..... @ManyToMany @JoinTable( name="MOD_PM_SCOPE_TYPES" , joinColumns={ @JoinColumn(name="PM_ID") } , inverseJoinColumns={ @JoinColumn(name="SCOPE_TYPE_ID") } ) private List<ModScopeType> modScopeTypes; 

ModScopeType:

 @Entity @Table(name="MOD_SCOPES") @Cacheable public class ModScopeType extends WebPageObject implements Serializable { private static final long serialVersionUID = 1L; public final static String Q_GET_ALL = "ModScopeType.getAll"; @Id @Column(name="ID") private long id; .... //bi-directional many-to-many association to ModPm @ManyToMany @JoinTable( name="MOD_PM_SCOPE_TYPES" , joinColumns={ @JoinColumn(name="SCOPE_TYPE_ID") } , inverseJoinColumns={ @JoinColumn(name="PM_ID") } ) private List<ModPm> modPms; 

Is it possible to select objects from the MOD_PM table that have an entry in the MOD_PM_SCOPE_TYPES table for SCOPE_TYPE_ID = 1

Native SQL query:

 SELECT ID, NAME FROM MOD_PM WHERE ID IN (SELECT PM_ID FROM MOD_PM_SCOPE_TYPES WHERE SCOPE_TYPE_ID=1) 

How can I translate this request to JP QL?

Answer 1

When you refer to SCOPE_TYPE_ID in a request, what are you referring to? Could be a ScopeType ID or another field that I do not see. I assume that the region type identifier field is missing.

Try the following:

 select p from ModPm p join p.modScopeTypes type where type.scopeTypeId = 1