I develop an admissions decision suppport system for use by tutors at a university with a collegiate system. Applicants ("candidates") specify a preferential order of colleges at which they wish to take their course, although it is not necessary to specify second or higher college preferences. Tutors are affiliated with colleges, and are often interested to see a list of all candidates for a given course. It is helpful to be able to sort this list by first preference college or sort by second preference college etc.
I inherited the application and am refactoring it to generalise the treatment of candidate data. I'm trying modify the sorting mechanism to use the Criteria interface. This works fine for Candidate attribute's not involving collections. However, I can't find a way to sort on the collegeCode of a CandidateCollege at a specified position in the Candidate's candidateCollegePreferences list. I understand this is problematic because there may not necessarily be a CandidateCollege at the specified position in candidateCollegePreferences. Ideally such candidates would be sorted as if the collegeCode was null.
Here's some SQL which does something like what I want (returning CANDIDATES even when they have no CAND_COLLEGE with cposition=2, but sorting them to the end):
Code:
select cand.appno, cand.surname,
(select candCollege.college_code from CAND_COLLEGE candCollege where candCollege.appno=cand.appno and candCollege.cposition=2)
as candCollege1
from CANDIDATE cand join COURSE crs on cand.appno=crs.appno
where crs.course_code='V118' order by candCollege1;
Here's the relevent mapping info. Some deviations from a fully normalized model have been necessary to allow speedy retrieval of large numbers of candidates (eg. the collegeCode property in place of an association with a College).
Code:
<hibernate-mapping>
<class name="ocao.wads.model.business.Candidate" table="CANDIDATE" lazy="true">
<id column="APPNO" name="appno" type="java.lang.Long" >
<generator class="assigned"/>
</id>
<list name="candidateCollegePreferences" cascade="all" table="CAND_COLLEGE" lazy="true">
<key column="APPNO"/>
<index column="CPOSITION" type="java.lang.Short"/>
<one-to-many class="ocao.wads.model.business.CandidateCollege"/>
</list>
<one-to-one name="candidateCourse" class="ocao.wads.model.business.CandidateCourse" constrained="true" fetch="join" cascade="all"/>
</class>
</hibernate-mapping>
Code:
<hibernate-mapping>
<class name="ocao.wads.model.business.CandidateCollege" lazy="true" table="CAND_COLLEGE">
<id column="CCKEY" name="cckey" type="java.lang.Long" unsaved-value="null">
<generator class="native">
<param name="sequence">SEQ_PK_CCOLLEGES</param>
</generator>
</id>
<property column="APPNO" name="appno" type="java.lang.Long"/>
<property column="COLLEGE_CODE" name="collegeCode" type="java.lang.String"/>
<property column="CPOSITION" name="cposition" type="java.lang.Short"/>
</class>
</hibernate-mapping>
I can't do the following, because this will sort candidates on the collegeCode of ALL of the CandidateColleges in their candidateCollegePreferences, with a candidate (A) seemingly being placed before another (B) when ANY of A's CandidateCollege's collegeCodes precede ALL of B's CandidateCollege's collegeCodes (correct me if I'm wrong).
Code:
sess.createCriteria(Candidate.class).createCriteria("candidateCollegePreferences").addOrder(Order.asc("collegeCode"));
Can anyone suggest how I can sort on the collegeCode of a CandidateCollege at a specified position in the Candidate's candidateCollegePreferences list?
Thanks in advance for any advice.
Callum
Login
FAQ
Search