Grails Createcriteria for subquery

Question

Hi,

How to use createCriteria for subQuery . My HQL is like this :

def lists = List.executeQuery("FROM List cl WHERE cl.brand_id in (SELECT b.id FROM Brand b WHERE cl.brand_id=b.id AND b.brand_name rLIKE '^[0123456789].*')")

Please tell how can we write this using Createcriteria ??

thanks in advance.

Answer 1

It seems that Hibernate doesn't support rlike or regexp feature (see http://opensource.atlassian.com/projects/hibernate/browse/HHH-3404) But Grails does !!

For that you need to used createCriteria (and not executeQuery) Based on this discussion http://n4.nabble.com/Using-rlike-in-a-gorm-query-td1391934.html and on http://jira.codehaus.org/browse/GRAILS-3481, your solution will be:

def c = List.createCriteria(); 
results = c { 
        rlike("name", "^[0123456789].*") 
} 

Attention: it might work only on MySQL and Oracle

Answer 2

When querying on a domain object's property you need to make a block in the criteria with the name of the property. Something like:

def criteria = List.createCriteria(); 
def results = criteria {
    brand {
        rlike("name", "^[0123456789].*") 
    }
}

See also the "Querying Associations" section in the Grails GORM criteria docs