|Difficult Query to be made in Eclipselink [message #1058266]
||Mon, 13 May 2013 12:17
| mohamed ibrahim mohamed
Registered: April 2013
Dear Friends , |
I have the following object graph
public class ReferralRequestEO implements Serializable
private List<ReferralSpecialtyEO> specialties;
public class ReferralSpecialtyEO implements Serializable
private WorkflowTransitionEO activeTransition;
public class WorkflowTransitionEO implements Serializable
private Date transitionDate;
private InstructionEO instruction;
private String state;
public class InstructionEO implements Serializable
private String instructionType;
According to the above graph , Each ReferralRequest has multiple ReferralSpecialties , Each ReferralSpecialty has an activeTransition of Type "WorkflowTransition" , Each workflowTransition has a transitionDate of type java.util.Date and a State of Type String.
Real World Scenario is as follows :
Each ReferralSpecialty receives different activeTransition at different times.
Now the problem is , I want to select all ReferralRequests which have the latest activeTransition which has a state of "Accepted" in JPQL.
A Real Scenario ,
I have a referral Request No.1300432 which has three ReferralSpecialties , One Specialty had rejected that request , So its ActiveTransition state is "Rejected" , Then after some time , another specialty had accepted that request , So this second specialty had an activeTransition with state "Accepted".
Now the problem is , when i try to query the database against the below query , the same request appears as rejected and accepted , but i want to say that this overall request is accepted because the latest activeTransition in the three referralSpecialties has a state of "Accepted".
This is the query i am using
"select Distinct r from ReferralRequestEO r join r.referralSpecialties rs where" +
" rs.activeTransition.transitionDate = (select max(trans.transitionDate) from WorkflowTransitionEO trans where " +
"trans = rs.activeTransition AND trans.state = ?1) AND rs.activeTransition.state = ?1 "+
"AND rs.request.archived = ?2"
+ " AND rs.request.active = ?3 ORDER BY rs.activeTransition.transitionDate DESC";
[Updated on: Mon, 13 May 2013 12:18]
Report message to a moderator
Powered by FUDForum
. Page generated in 0.04991 seconds