Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Difficult Query to be made in Eclipselink
Difficult Query to be made in Eclipselink [message #1058266] Mon, 13 May 2013 12:17
mohamed ibrahim mohamed is currently offline mohamed ibrahim mohamedFriend
Messages: 6
Registered: April 2013
Junior Member
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 = ?3 ORDER BY rs.activeTransition.transitionDate DESC";

[Updated on: Mon, 13 May 2013 12:18]

Report message to a moderator

Previous Topic:Sorting by Date , I can't solve it
Next Topic:[Solved] P2 Repository Download failure
Goto Forum:

Current Time: Thu Apr 26 04:15:27 GMT 2018

Powered by FUDForum. Page generated in 0.02237 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software