Grails on Groovy > Create Criteria > Create Alias > Grails Create Alias > Create Alias With Additional Criteria

We frequently use createAlias to create alias between entity. It actually create connection based on foreign key and primary key between two entities. Below is a example how we create alias between entities:

Table1.createCriteria().list { createAlias("scores", "scores") }

Which will produce SQL like below where we can see that foreign key of table1 create a link with table2_child based on primary key id as following:

select ... from table1 this_ inner join table2_child scores1_ on (this_.id=scores1_.table1_id)

But if we need more on add to filter when joining them, yeah, we can do that. We can add extra conditions to on part of that SQL. To do so, first need to create a groovy file like below:

package com.pkm import org.hibernate.Criteria import org.hibernate.HibernateException import org.hibernate.criterion.CriteriaQuery import org.hibernate.criterion.Criterion import org.hibernate.engine.spi.TypedValue class CustomSqlJoin implements Criterion { private Double score = null; CustomSqlJoin(Double score) { this.score = score } //Use Example > createAlias("scores", "scores", JoinType.INNER_JOIN, new CustomSqlJoin(2D)) @Override String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { String alias = criteriaQuery.getSQLAlias(criteria) return "${alias}.score > ${this.score}" } @Override TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { return new TypedValue[0] } }

And you can use the above custom condition using as below example:

Table1.createCriteria().list { createAlias("scores", "scores", JoinType.INNER_JOIN, new CustomSqlJoin(2D)) "ne" "id", System.currentTimeMillis() }

Which will generate below SQL

select ... from table1 this_ inner join table2_child scores1_ on this_.id=scores1_.table1_id and ( scores1_.score > 2.0 ) where this_.id<>?

Grails Create Criteria | Grails CreateCriteria | Custom Criteria | Custom Criteria Condition

Grails Create Criteria | Grails CreateCriteria | Custom Criteria | Custom Criteria Condition

You have to create a groovy class as below:

import org.hibernate.Criteria
import org.hibernate.HibernateException
import org.hibernate.criterion.CriteriaQuery
import org.hibernate.criterion.Criterion
import org.hibernate.engine.spi.TypedValue
import org.hibernate.internal.util.StringHelper

/**
 * Created by pritom on 25/09/2017.
 */
class CustomCriteria implements Criterion {
    private static final TypedValue[] NO_VALUES = new TypedValue[0]
    private final String propertyName

    CustomCriteria(String propertyName) {
        this.propertyName = propertyName
    }

    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        String[] columns = criteriaQuery.findColumns(this.propertyName, criteria)
        String result = StringHelper.join(" or ", StringHelper.suffix(columns, " is not null"))
        if(columns.length > 1) {
            result = '(' + result + ')'
        }

        return result
    }

    public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return NO_VALUES
    }

    public String toString() {
        return this.propertyName + " is not null"
    }
}

Then you to attach those functions as below:

Object.metaClass.with {
    customCriteria = {
        String propertyName = delegate.delegate.calculatePropertyName(it)
        delegate.delegate.addToCriteria(new CustomCriteria(propertyName))
        return delegate.delegate
    }
}

Then you can use your custom criteria function as below:

return (List<Home>) Home.createCriteria().list {
    setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
    createAlias("childs", "childs")
    isNotNull("childs.id")
    customCriteria("childs.id")
    not { isNull("childs.id") }
}