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 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

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


Current Time: Wed Sep 17 09:38:44 GMT 2014

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

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