Question about UNION two tables with plain SQL in Grails and Pagination.
I solve my [QUESTION][2] by referencing this [ANSWER][1]. But the way that I implement pagination is too ugly... I would like to know if there is any better/compact/easier way to do the same? Thanks!!

My controller is:

    import groovy.sql.Sql

class T1T2Controller {

    T1 t1
    T2 t2

    def dataSource // the Spring-Bean "dataSource" is auto-injected

    def index = {
        redirect(action: "list", params: params)
    }

    def list = {
        def db = new Sql(dataSource) // Create a new instance of groovy.sql.Sql with the DB of the Grails app

        def sqlStatement = "SELECT A1 AS A, B1 AS B, C1 AS C, D1 AS D FROM T1 "+
                     "UNION "+
                     "SELECT A2 AS A, B2 AS B, C2 AS C, D2 AS D FROM T2 "+
                     "ORDER BY D DESC;";

        def list_rs = db.rows(sqlStatement) // Perform the query    
        def list_rs_offset=[]
        
        params.max = Math.min(params.max ? params.int('max') : 2, 100)
        
        if (params.offset!=null){
            int maxPos=Integer.valueOf(params.offset)+params.max;
            for (int i=Integer.valueOf(params.offset); i<maxPos; i++){
                list_rs_offset.push(list_rs.get(i));
            }
        }else{
            for (int i=0; i<params.max; i++){
                list_rs_offset.push(list_rs.get(i));
            }
        }

        [ t1T2InstanceList: list_rs_offset, t1T2InstanceTotal: list_rs.size()]
    }
}


  [1]: http://stackoverflow.com/questions/425294/sql-database-views-in-grails/
  [2]: http://stackoverflow.com/questions/6549010/in-grails-how-to-union-two-tables-in-a-single-controller
Question about UNION two tables with plain SQL in Grails and Pagination.
===

I solve my [QUESTION][2] by referencing this [ANSWER][1]. But the way that I implement pagination is too ugly... I would like to know if there is any better/compact/easier way to do the same? Thanks!!

My controller is:

    import groovy.sql.Sql

class T1T2Controller {

    T1 t1
    T2 t2

    def dataSource // the Spring-Bean "dataSource" is auto-injected

    def index = {
        redirect(action: "list", params: params)
    }

    def list = {
        def db = new Sql(dataSource) // Create a new instance of groovy.sql.Sql with the DB of the Grails app

        def sqlStatement = "SELECT A1 AS A, B1 AS B, C1 AS C, D1 AS D FROM T1 "+
                     "UNION "+
                     "SELECT A2 AS A, B2 AS B, C2 AS C, D2 AS D FROM T2 "+
                     "ORDER BY D DESC;";

        def list_rs = db.rows(sqlStatement) // Perform the query    
        def list_rs_offset=[]
        
        params.max = Math.min(params.max ? params.int('max') : 2, 100)
        
        if (params.offset!=null){
            int maxPos=Integer.valueOf(params.offset)+params.max;
            for (int i=Integer.valueOf(params.offset); i<maxPos; i++){
                list_rs_offset.push(list_rs.get(i));
            }
        }else{
            for (int i=0; i<params.max; i++){
                list_rs_offset.push(list_rs.get(i));
            }
        }

        [ t1T2InstanceList: list_rs_offset, t1T2InstanceTotal: list_rs.size()]
    }
}


  [1]: http://stackoverflow.com/questions/425294/sql-database-views-in-grails/
  [2]: http://stackoverflow.com/questions/6549010/in-grails-how-to-union-two-tables-in-a-single-controller