Hibernate criteria subquery referring to value from "root" query

Question

I'm working in Grails & Groovy but this is a hibernate question. Here's my Grails domain class:

public Card {
    // implicit Long id
    String name

    // ... and over a dozen other fields with various types.
}

I currently have this Hibernate query:

def name = "foo"

def result = session.createQuery("from Card as c where lower(c.name) like ? and c.id in (select max(c2.id) from Card as c2 where c.name = c2.name)")
        .setString(0, "%${name}%")
        .list()

This gets all Cards with names containing the substring "foo", skipping over any Cards with duplicate names except for the "newest" (I'm assuming that higher ID means 'newer'). I must avoid returning Cards with duplicate names.

In addition to filtering on the name and dodging the duplicate-names problem, I need to filter and/or sort on the other fields in Card. I also need to paginate. I wanted to use the Criteria API to do this, since the approach of generating SQL/HQL leads to maintainability nightmares.

I just can't understand how to do this kind of querying via Criteria API though. There are the detached queries that can be used as a subquery via the Criteria API. However these subqueries all seem to be totally independent of the primary query - I can't get at 'c.name' to compare it to 'c2.name'.

What am I missing here?

score 0 · Answer 1 · edited May 23 '17 at 12:30

Figured this out myself - there's a Criterion available through Restrictions.sqlRestriction that allows custom SQL to be used in criteria. Here's the criteria equivalent to my HQL in the 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 some kind of bug with the alias-resolution in sqlRestriction() - supposedly I can use {c2}.name in the expression and have Hibernate generate correct SQL, but I found I had to manually write out this_ and c2_ for the correct SQL to generate.

Look at How to log SQL statements in Grails to see how to log the generated SQL and discover the correct names to use for your own database.

score 0 · Answer 2 · answered Dec 11 '15 at 08:09

There is another possibility, to use a value from your criteria in your subcriteria (in my opinion more clearer):

def name = 'foo'

def crit = session.createCriteria(Card.class, 'c1')
def subcrit = DetachedCriteria.forClass(Card.class, 'c2')

subcrit.add(Restrictions.eqProperty('c1.name', 'c2.name'))
subcrit.setProjection(Projections.max('c1.id'))

crit.add(Restrictions.ilike('c1.name', "%${name}%"))
    .add(Subqueries.propertyIn('c1.id', subcrit))

def result = crit.list()

Make sure that you place an alias e.g. c1 for your main-criteria and use the restriction function eqProperty() to compare both values.

Hibernate criteria subquery referring to value from "root" query

2 Answers2