Query model by association

[Arvind]

I'm going to take a Patient->>Study->>Series->>Image model that is
similar to the Blog->>Post->>Comment model in the usual Ayende
example.
The mapping is as follows:

Patient-------
<class name="Patient" table="PATIENTS" lazy="false">

<!-- Identity mapping -->
<id name="ID" unsaved-value="0">
<column name="ID" />
<generator class="native">
<param name="sequence">PATI_SEQ</param>
</generator>
</id>

<property name="PatientsBirthTime" type="string"
column="PATIENTS_BIRTH_TIME" />
<property name="PatientsName" type="string" not-null="true"
column="PATIENTS_NAME" />


<property name="PatientsBirthDate" type="Date"
column="PATIENTS_BIRTH_DATE" />

<set name="StudyList" cascade="delete-orphan" lazy="true"
inverse="true">
<key column="PATI_ID" />
<one-to-many class="Study" />
</set>

</class>

Study---------
<class name="Study" table="STUDIES" lazy="false">

<!-- Identity mapping -->
<id name="ID" unsaved-value="0">
<column name="ID" />
<generator class="native">
<param name="sequence">SSTUD_SEQ</param>
</generator>
</id>

<property name="StudyInstanceUID" type="string" not-null="true"
column="STUDY_INSTANCE_UID" />
<property name="StudyTime" type="string" column="STUDY_TIME" />
<property name="StudyDate" type="DateTime" not-null="true"
column="STUDY_DATE" />

<many-to-one name="Patient"
cascade="none"
class="Patient"
column="PATI_ID"
not-null="true"/>

<set name="SeriesList" cascade="delete-orphan" lazy="true"
inverse="true">
<key column="STUD_ID" />
<one-to-many class="WPSeries" />
</set>

<set name="StatesList" cascade="delete-orphan" lazy="false"
inverse="true">
<key column="STUD_ID" />
<one-to-many class="WPState" />
</set>

<set name="StaffList" cascade="delete-orphan" lazy="false"
inverse="true">
<key column="STUD_ID" />
<one-to-many class="WPStaffInstance" />
</set>

</class>


Series-----------
<class name="Series" table="SERIES" lazy="false">

<!-- Identity mapping -->
<id name="ID" unsaved-value="0">
<column name="ID" />
<generator class="native">
<param name="sequence">SERI_SEQ</param>
</generator>
</id>

<property name="SeriesInstanceUID" type="string" not-null="true"
column="SERIES_INSTANCE_UID" />
<property name="SeriesDate" type="Date" column="SERIES_DATE" />

<one-to-one name="Report" cascade="all" class="WPReport" property-
ref="Series" lazy="false" fetch="join"/>

<set name="ImageList" cascade="delete-orphan" lazy="false"
fetch="join" inverse="true">
<key column="SERI_ID" />
<one-to-many class="Image" />
</set>

<many-to-one name="Study"
cascade="none"
class="Study"
column="STUD_ID"
not-null="true"/>

<many-to-one name="Equipment"
cascade="none"
class="WPEquipment"
column="EQUI_ID"/>

</class>



And finally Image-------
<class name="WPImage" table="IMAGES" lazy="false">

<!-- Identity mapping -->
<id name="ID" unsaved-value="0">
<column name="ID" />
<generator class="native">
<param name="sequence">IMG_SEQ</param>
</generator>
</id>

<property name="AquisitionDate" type="DateTime"
column="AQUISITION_DATE" />
<property name="SOPClassUID" type="string" column="SOP_CLASS_UID" /
>
<property name="SOPInstanceUID" type="string"
column="SOP_INSTANCE_UID" />

<many-to-one name="Series"
cascade="none"
class="WPSeries"
column="SERI_ID"
not-null="true"/>

</class>



Now with these wonderful models in mind -
I actually need to query a list of PATIENTS but by providing Patient/
Study/Series/Image search parameters.
Largely not a big problem since each CAN be created separately as a
DetachedCriteria and connected via subqueries.
The problem though is - How do I sort the list of PATIENTS I get by
the StudyDate????
If I try to create a study criteria FROM the Patient criteria ->
patientCriteria.CreateCriteria("StudyList",JoinType.InnerJoin) -
this CAN give me capability by sort by studyDate on Patients (due to
the join) ...
BUT - due to the Many-to-one mapping from Study to Patient it does
ANOTHER join back from Study to Patient!
1) How do I avoid this?
2) How do I avoid a Cartesian product?
3) as an addendum - what is the best way to grab this entire tree out
of the DB without cascading into a whole boatload of N+1 SQL
queries???

Thank you for the help!