| Difficult Query to be made in Eclipselink [message #1058266] |
Mon, 13 May 2013 12:17 |
 mohamed ibrahim mohamed
Messages: 6
Registered: April 2013 |
Junior Member |
|
|
Dear Friends ,
I have the following object graph
@Entity
....
public class ReferralRequestEO implements Serializable
{
....
private List<ReferralSpecialtyEO> specialties;
}
@Entity
....
public class ReferralSpecialtyEO implements Serializable
{
......
private WorkflowTransitionEO activeTransition;
}
@Entity
.....
public class WorkflowTransitionEO implements Serializable
{
......
@Temporal(Temporal.TimeStamp)
private Date transitionDate;
@oneToOne
joinColumn(...)
private InstructionEO instruction;
@Column(name="state")
private String state;
}
@Entity
.....
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.02829 seconds
] 
Search
Help
Register
Login
Home
