Ok, so this is probably a rare case, but what I'm trying to do is this:

There's an Exam entity and a TakenExam entity. The TakenExam entity references the Exam entity and a User who took the exam. There may be many TakenExam's for every Exam (or none). I'm trying to build a report of the available exams for a particular user with the information about whether they've passed the exam before, etc. Right now I'm doing this with an HQL query like this:



The association is one way, there's no association from Exam to TakenExam, so I'm doing a right outer join to get all of the exams with their corresponding TakenExams if they exist.

I'd really like to be able to model this using the Criteria API using the ResultTransformer to create my DTO object like the "select new" is doing above, but, although I found some posts about left outer join support in the Criteria API (and it only seems to be in the Criteria, not DetachedCriteria for some reason), there's nothing I could find for right outer join support. I'd like to create a DetachedCriteria for this so I can easily add more filters and ordering to the query at runtime. We've already got a bunch of infrastructure around adding these to a Criteria, and I don't relish doing string concatenation to try to get this working.

Any help appreciated!

Hmm... I've also noticed that adding the user to the where clause is keeping the right outer join from getting Exams without TakenExams.... I can see how I could do this with SQL by adding the restriction of the user to the "on" of the right outer join, but I like the HQL "select new"...

Is there a way to specify default values for selected values? Here's my current query, which works around the problem of the condition on TakenExam killing the outer-joinness of the outer join:



I was trying to do this using a "select new eg.ExamInfo(...)" but I found that if the TakenExam is null (which is ok as per the right outer join) then the number of objects in the Object[] returned by the query is 4, not 6. It doesn't fill in the last 2 columns with null, it returns an array with only 4 elements, which, of course, breaks when it's passed to the constructor which is expecting 6...

Any ideas?

Also, I'd still like to be able to do this using the Criteria API, if anyone has any thoughts...