How to verify if a field is an empty string with GORM?

Question

I have a GORM query and I need to make it avoid a field which may be a empty string for legacy system questions.

Currently, my query is:

def things = Thing.withCriteria{
    resultTransformer CriteriaSpecification.ALIAS_TO_ENTITY_MAP
    createAlias 'factor', 'f'
    eq 'active', true
    isNotNull 'title' // this field may be an empty string
    isNotNull 'content' // this field also may be an empty string
    // <-- and here I'd like to include sth like isEmpty 'title'
    not {
        inList 'f.id', factors
    }
    projections{
        property 'id'       , 'id'
        property 'f.name'   , 'name'
        property 'title'    , 'title'
        property 'content'  , 'content'
        property 'f.id'     , 'factorId'
    }           
}

I didn't find any solution in the docs, but I have a feeling that this solution should be with pure SQL. Is it possible? Any suggestion?

Answer 1

Have you tried adding this statement to the criteria?

ne title, ""

Grails criteria relies on hibernate Restrictions, so everything supported by this can also be used in grails criteria.

Otherwise you could also use sql restrictions (see Using SQL Restrictions section):

sqlRestriction "char_length(title) > 0"

Keep in mind that title here refers to the real column name in the database. If you want to have a more reliable code, you can include a static mapping for the field (column name) in the model :

static mapping = {
     title column: 'title'
}

In this way you should avoid errors from db specific naming behaviour (if any).