First of all clarify that I am quite bad with databases, so please do not be to mean with my code :P
I have a problem with a query on hibernate using join and restrictions. I have a huge list of Assignments
and some of them have an Asr
object.
queryString.append("select new commons.bo.assignment.AssignmentAssociateExtract("
+ " assignment.id, assignment.contract.assignmentStatus,"
+ " assignment.contract.beginDate, assignment.contract.endDate,"
+ " assignment.contract.contractType, assignment.organizationalData.homeCountryKey,"
+ " assignment.organizationalData.hostCountryKey,"
+ " assignment.organizationalData.homeOrgUnitKey,"
+ " assignment.associate.globalIdAssociate,"
+ " assignment.associate.localIdHome,"
+ " assignment.associate.firstName,"
+ " assignment.associate.lastName)"
+ " from Assignment assignment left join assignment.asr asr"
+ " where assignment.contract.assignmentStatus.code = 5"
+ " and asr.homeAsrElegibility is not 'X'"
+ " or asr.homeAsrElegibility is null"
);
I was creating this query step by step. Before I created it without the join clause left join assignment.asr asr
and it was working well but of course it was not showing the Assignments
that did not have a Asr
object. After I added the join clause, now it shows every single Assignment
(10.000 records when those who have an assignmentStatus = 5
are just 4.000) and the restrictions like
where assignment.contract.assignmentStatus.code = 5
are not reflected in the result anymore.
So to sum up: I need a list with all assignments with assignmentStatus = 5 and asr.homeAsrElegibility != 'X'. But it needs to include also all assignments with assignmentStatus = 5 even if they do not have an Asr object.
Any ideas?? Thanks!
Parenthesis helps to clarify the situation.
queryString.append("select new commons.bo.assignment.AssignmentAssociateExtract("
+ " assignment.id, assignment.contract.assignmentStatus,"
+ " assignment.contract.beginDate, assignment.contract.endDate,"
+ " assignment.contract.contractType, assignment.organizationalData.homeCountryKey,"
+ " assignment.organizationalData.hostCountryKey,"
+ " assignment.organizationalData.homeOrgUnitKey,"
+ " assignment.associate.globalIdAssociate,"
+ " assignment.associate.localIdHome,"
+ " assignment.associate.firstName,"
+ " assignment.associate.lastName)"
+ " from Assignment assignment left join assignment.asr asr"
+ " where assignment.contract.assignmentStatus.code = 5"
+ " and ((asr.homeAsrElegibility is not null and asr.homeAsrElegibility is not 'X')"
+ " or (asr.homeAsrElegibility is null))"
);