Code Samples: join-tables

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

Wednesday, October 26, 2022

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