JPA select from collection with many-to-many association

Question

I have 2 Entity classes with 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 entities from MOD_PM table which have record in 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 query to JPQL?

Answer 1

When you refer to SCOPE_TYPE_ID in the query what are you referring to? Could be the ID of ScopeType or another field that I'm not seeing. I'm assuming the scope type id field is missing.

Try this:

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