Grails on Groovy - join tables using createAlias with additional condition along with sql on condition for additional filter of data

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 AliasBuiilder.groovy file like below:

package com.gradle_app import org.hibernate.criterion.Criterion import org.hibernate.sql.JoinType abstract class AliasBuilder { static final Object synk0 = new Object() static Long number = 0L static void fixCriteriaBuilderProps(criteriaBuilder) { if (!criteriaBuilder.properties.containsKey("aliases")) { criteriaBuilder.metaClass.aliases = [:] criteriaBuilder.metaClass.data = [:] } criteriaBuilder = null } static String createAlias0(criteriaBuilder, String field, String aliasPath = field, JoinType joinType = JoinType.INNER_JOIN, Criterion customCriterion = null) { aliasPath = aliasPath ? aliasPath : field joinType = joinType ? joinType : JoinType.INNER_JOIN fixCriteriaBuilderProps(criteriaBuilder) def subCriteria = criteriaBuilder.criteria.subcriteriaList.find { it.path == aliasPath } if(subCriteria) { try { return subCriteria.alias } finally { criteriaBuilder = field = aliasPath = joinType = subCriteria = null } } else { String alias = "${field}_custom_name_${nextSerialNumber()}".toLowerCase() criteriaBuilder.createAlias(aliasPath, alias, joinType, customCriterion) String other = alias.substring(0, criteriaBuilder.sessionFactory.dialect.getMaxAliasLength()) + (criteriaBuilder.properties.aliases.size() + 1) + "_" criteriaBuilder.properties.aliases[alias] = other criteriaBuilder.properties.data[aliasPath] = alias try { return alias } finally { criteriaBuilder = field = aliasPath = joinType = subCriteria = alias = other = null } } } static String nextSerialNumber() { Long serial = null try { synchronized (synk0) { number = serial = number + 1L if (number == Long.MAX_VALUE) { number = 1L } } return serial.toString() } finally { serial } } }

And another CustomCriteria.groovy file like this: package com.gradle_app 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 class CustomCriteria implements Criterion { private static final TypedValue[] NO_VALUES = new TypedValue[0] public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { String alias = criteriaQuery.getSQLAlias(criteria) return "${alias}.status='1'" } public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { return NO_VALUES } }

And you can use the above custom condition using as below example: void checkCustomCriteria() { List list = Table1.createCriteria().list { //setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) String scores = AliasBuilder.createAlias0(delegate, "scores", "scores", JoinType.LEFT_OUTER_JOIN, new CustomCriteria()) "projections" { "groupProperty" "id", "id" addProjectionToList(Projections.sqlProjection( "GROUP_CONCAT(${delegate.aliases[scores]}.id) as scores", ["scores"] as String[], [StringType.INSTANCE] as Type[] ), "scores") } } list.each { dataRow-> println("Data row=${dataRow}") } }

Which will generate below SQL with additional condition on statement ( scores1_.status='1' ) select ... from table1 this_ inner join table2_child scores1_ on this_.id=scores1_.table1_id and ( scores1_.score.status='1' ) where this_.id<>?

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