Grails: Adding sorting and paging to a custom HQL query

Question

I have a list method that uses HQL. how do I add the paging and sorting parameters 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 documentation:

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

Answer 2

I ran into the same issue; while you can specify max and offset in the executeQuery params, sort and order will be ignored. To get this to work, I had to specify sort and order in the HQL query itself. The resulting query should look something like:

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

First, in your sortable columns you will need to change the property attribute to reference the domain you are querying. So for "Account a", do this:

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

Next, you will need to alter your HQL query. Unfortunately, it appears that you can't use named parameters in the order by clause, so you'll need to do it manually. It would probably be a good idea to sanitize params.order and params.sort first.

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

This worked for me, I really hope there's a better way out there.