I'm trying to inject a sub query into my Criteria based query. This sub query is actually a join of three tables...
Code:
Expert --> Training episodes --> Training Class
The code is like...
Code:
DetachedCriteria aSubQuery = DetachedCriteria.forClass(FullExpert.class,"expert_sub");
DetachedCriteria anEpisode = aSubQuery.createCriteria("trainingEpisodes","trainingEpisode_sub");
DetachedCriteria aTrainingClass = anEpisode.createAlias("trainingClass","trainingClass_sub");
Conjunction j = Restrictions.conjunction();
for (int i=0; i < aParams.getList().size(); i++) {
Criterion h = null;
TrainingClassSearchParameters aSearch = aParams.getList().get(i);
Criterion a = Restrictions.eq("trainingClass_sub.ID",aSearch.getClassID());
Criterion b = null;
if (aSearch.isPassed() == true) {
b = Restrictions.eq("trainingEpisode_sub.passed",true);
}
Criterion c = null;
if (aSearch.hasPeriod()) {
c = Restrictions.ge("trainingEpisode_sub.attended",getPeriodDateBack(aSearch.getPeriod()));
}
if ((b != null) || (c != null)) {
Conjunction d = Restrictions.conjunction();
d.add(a);
if (b != null) {
d.add(b);
}
if (c != null) {
d.add(c);
}
h = d;
}
else {
h = a;
}
j.add(h);
}
aSubQuery.add(j);
aSubQuery.setProjection(Projections.projectionList().add(Projections.groupProperty("expert_sub.ID"))
.add(Projections.count("expert_sub.ID").as("expertRowCount"))
);
aSubQuery.add(Restrictions.eq("expertRowCount",aParams.getList().size()));
aWorking.getTop().add(Subqueries.exists(aSubQuery));
In the resulting SQL query the sub query does not include the required table joins as defined using createCriteria and createAlias.
A couple of other questions....
* How do I restrict the fields returned by a Criteria query? Say I want just two fields, main ID and title rather than all the class fields?
* Can I use a HQL based query to represent the sub query? If so, how would I insert this into the main Criteria?
Login
FAQ
Search
