groovy - Hibernate criteria subquery referring to value from "root" query -

i'm working in grails & groovy hibernate question. here's grails domain class:

public card {     // implicit long id     string name      // ... , on dozen other fields various types. } 

i have hibernate query:

def name = "foo"  def result = session.createquery("from card c lower(c.name) ? , c.id in (select max(c2.id) card c2 c.name = c2.name)")         .setstring(0, "%${name}%")         .list() 

this gets cards names containing substring "foo", skipping on cards duplicate names except "newest" (i'm assuming higher id means 'newer'). i must avoid returning cards duplicate names.

in addition filtering on name , dodging duplicate-names problem, need filter and/or sort on other fields in card. need paginate. wanted use criteria api this, since approach of generating sql/hql leads maintainability nightmares.

i can't understand how kind of querying via criteria api though. there detached queries can used subquery via criteria api. these subqueries seem totally independent of primary query - can't @ 'c.name' compare 'c2.name'.

what missing here?

figured out myself - there's criterion available through restrictions.sqlrestriction allows custom sql used in criteria. here's criteria equivalent hql in question:

def name = 'foo'  def crit = session.createcriteria(card.class) def subcrit = detachedcriteria.forclass(card.class, 'c2')  subcrit.add(restrictions.sqlrestriction('this_.name = c2_.name')) subcrit.setprojection(projections.max('id'))  crit.add(restrictions.ilike('name', "%${name}%"))     .add(subqueries.propertyin('id', subcrit))  def result = crit.list() 

there's kind of bug alias-resolution in sqlrestriction() - supposedly can use {c2}.name in expression , have hibernate generate correct sql, found had manually write out this_ , c2_ correct sql generate.

look @ how log sql statements in grails see how log generated sql , discover correct names use own database.