I have 3 Entities, Mouvement links Agent and Poste. I can't join directly poste and agent because it depends on the date of the movement.
@NamedEntityGraph(name = "graph.Agent.poste",
attributeNodes = @NamedAttributeNode(value = "poste"))
@Entity
@Table(name = "agent")
public class Agent extends RestaurableEntity<Long> {
//fields firstname lastname birthdate...
@ManyToOne(fetch = FetchType.LAZY)
@JsonInclude(JsonInclude.Include.NON_NULL)
@JoinFormula("(" +
"SELECT TOP 1 CASE WHEN (tm.arrivee=1 ) THEN m.id_poste ELSE (NULL) END " +
"FROM mouvement m, type_mouvement tm " +
"WHERE m.id_agent = id " +
"AND m.id_type_mouvement=tm.id "+
"AND m.supprime=0 "+
"AND m.date<=GETDATE() "+
"ORDER BY m.date DESC " +
")")
private Poste poste;
}
@Entity
@Table(name = "poste")
@NamedEntityGraph(name = "graph.Poste.agent",
attributeNodes = @NamedAttributeNode(value = "agent", subgraph = "agent"))
public class Poste extends RestaurableEntity<Integer> {
@ManyToOne(fetch = FetchType.LAZY)
@JoinFormula("(" +
"SELECT TOP 1 CASE WHEN (tm.arrivee=1) THEN m.id_agent ELSE (NULL) END " +
"FROM mouvement m, type_mouvement tm " +
"WHERE m.id_poste = id " +
"AND m.id_type_mouvement=tm.id " +
"AND m.supprime=0 " +
"AND m.date<=GETDATE() " +
"ORDER BY m.date DESC " +
")")
private Agent agent;
}
@Entity
@Table(name = "mouvement")
public class Mouvement extends RestaurableEntity<Long> {
@Column(name = "date", nullable = false)
private Date date;
@ManyToOne
@JoinColumn(name = "id_agent")
private Agent agent;
@ManyToOne
@JoinColumn(name = "id_poste", nullable = false)
private Poste poste;
}
@Transactional
public interface AgentRepository extends JpaRepository<Agent, Long> {
@EntityGraph(value="graph.Agent.poste",type = EntityGraph.EntityGraphType.LOAD)
@Query("select a from Agent a")
List<Agent> findAllWithPoste();
}
My problem is when I call findAllWithPoste() I retrive the agent list with their poste but I also get the agent inside the poste which I don't want to load in order to save time. Does any one know how to load poste without loading the agent inside the poste ?
PS: I've already tried to transform my JoinFormula into a repository method but it didn't worked.
@Query("select new Agent(a,( select limit 1 p from Mouvement m, Poste p where m.agent.id=a.id and m.id_poste=p.id and m.date<= current_date() order by m.date desc) ) from Agent a")
List<Agent> findAllWithPoste();