Hibernate createCriteria JOIN, ORDER, DISTINCT from one level

Question

I have 3 classes: first, second, third I have createria approximately like this:

First.createCriteria().listDistinct {
            if(....){fetchMode("second", FetchMode.JOIN)
            fetchMode("second.third", FetchMode.JOIN)}
            order(sortOrder, orderBy)
            order("id", "desc")
}

(when "if" false, createCriteria works fine)

when "if" true Hibernate generated sql request like this

select 
  distinct id,
  secondfield
from
  FIRST as firsts
inner join
  SECOND
       ... on ...
inner join
  THIRD
       ... on ...
where(...)
order by 
     firsts.name asc
     firsts.id desc

But so sql request cannot be executed because (distinct, join, order by) cannot be on the same level togehter. I lunch it in SQL developer. This request without one of (distinct, join, order by) works fine. What can I do with this. May by I can coerce hibernate do (join, distinct) and (order) in "different SELECT" levels? How i can wrap SELECT by another SELECT?

select *
from 
   (select 
     distinct id,
     secondfield
   from
     FIRST as firsts
   inner join
     SECOND
          ... on ...
   where(......))
order by
    (......)

Answer 1

You can try Groovy.SQL.* which gives you a lot moore flexibility than HQL queries.

Try this: http://groovy.codehaus.org/Tutorial+6+-+Groovy+SQL