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<>?