Grails: Add Sort and Swap to a HQL Custom Query

Question

I have a list method that uses HQL. How to add swap and sort options to this query?

   def list = {
         def termList
        log.info "Getting terms for product: $params.productId"
        params.max = 10
        def p = Product.get(params.productId)
        if (p) {
            log.info "Product found: $p.name"
            termList = Term.executeQuery("select distinct t from Term as t join t.definitions def join def.definitionProducts dp where dp.product=?",
                p )

        } else {
            log.info "No Product found"
            termList = Term.list(params)

        }
        log.info "Terms found: $termList.size"

        [ termInstanceList: termList, termInstanceTotal: termList.size]
    }

Answer 1

From the documentation:

// use with the Map named parameters and pagination parameters (starting with 0.5) Account.executeQuery ("select a.number from Account a where a.branch =: branch", [branch: 'London', max: 10 , offset: 5]);

Answer 2

; max offset executeQuery, . , HQL. :

"select distinct a.number from Account a where a.branch = :branch order by a.id asc"

-, , . , " a" :

<g:sortableColumn property="a.id" title="Id"/>

HQL. , , order by, . , params.order params.sort.

"select distinct a.number from Account a where a.branch = :branch order by " +  params.sort + " " params.order

, , .

Answer 3

SQL.

:

params.sort =
    params.sort in ['id','name'] ? params.sort:'name'
params.order =
    params.order in ['asc','desc'] ? params.order:'asc'

SQL-

String sql = """
   SELECT NEW MAP(c.id as id, c.name as name) FROM Customer c
   WHERE c.partner.id = :id
   ORDER BY c.$params.sort $params.order
""".stripMargin()

, , executeQuery

def customers = Customer.executeQuery(
    sql, 
    [id:params.long('id')],
    [max:params.long('max'),offset:params.long('offset')]
)