Hibernate ManyToMany select

Question

Context: I have two tables Secret_Agent and Secret_Mission. Both have a @ManyToMany relationship with each other since many secret agents can be given to perform the same secret mission and the same secret agent can be given many secret missions.

Table SECRET_AGENT

Columns SecretAgentId, SecrentAgentName

Table SECRET_MISSION

Columns SecretMissionId, SecrentMissionName, SecretMissionStatus

JOIN Table

SECRET_AGENT_MISSION

Columns: SecretAgentId,SecretMissionId

Java Code:

class Secret_Agent { . . .

@ManyToMany(cascade = CascadeType.ALL) @JoinTable(name = "SECRET_AGENT_MISSION", joinColumns = { @JoinColumn(name = "SecretAgentId") }, inverseJoinColumns = { @JoinColumn(name = "SecretMissionId") } private List missions; . . . }

class Secret_Mission { . . .

@ManyToMany(mappedBy = "missions") private List agents; . . . }

problem: I`m trying to get all Agents and agents mission with status = Active. But query below retrieve me just Agents who had missions with status Active.

 @Query(FROM FROM Secret_Agent sa "
            + "LEFT JOIN FETCH sa.missions sm"
            + "WHERE sm.status = "ACTIVE" ")

There mission status can be Archieve or Active. I need just All Secret Agents with missions which had status ACTIVE or just SecretAgent entity with empty mission

Answer 1

It is possible to solve this problem by mapping your association twice in the following way with @Where annotation.

@ManyToMany(mappedBy = "agents") 
@Where(clause = "status = 'ACTIVE'")
private List activeMissions

@ManyToMany(mappedBy = "agents")
private List missions

Complete explanation can be found here https://thoughts-on-java.org/hibernate-tips-filter-entities-mapped-association/