CODEWITHSUNDEEP
hibernategrailssql-order-byhql
ubiquibacon
ubiquibacon

Reputation: 10667

How to ORDER BY in Grails HQL statement

I have a relationship like this:

class Foo {
    static hasMany = [bars: Bar, things: Thing]
}

class Bar {
    // Has nothing to tie it back to Foo or Thing
}

class Thing {
    // Has nothing to tie it back to Foo or Bar
}

enter image description here

I have the following query with the following parameters, most of which are used for pagination with flexigrid. This query gets all instance of Thing that are associated with a specific instance of Bar via Foo. So if my instance of Bar was Bar1 would expect my query to return Thing1 and Thing2:

def obj = Bar.get( 1 ) // Get Bar1
def max = params.int( "max" ) ?: 100 // Default max returned results is 100 unless otherwise specified
def offset = params.int( "offset" ) ?: 0 // Default offset is 0 unless otherwise specified
def sortname = params.sortname ?: "id" // Default is id, but could be any member of Thing that is not a "hasMany"
def sortorder = params.sortorder ?: "ASC" // Default is ASC, but could be DESC

def namedParams = [ obj: obj, max: max, offset: offset ]

Thing.executeQuery( "SELECT DISTINCT f.things FROM Foo f INNER JOIN f.things things INNER JOIN f.bars bars WHERE bars =:obj ORDER BY ${sortname} ${sortorder}", namedParams )

Hibernate does not allow the usage of named parameters to specify the ORDER BY clause, so I just interpolated the string. The problem is that the results are not ordered as I have specified. When using ORDER BY id Grails tells me id is ambiguous.

Knowing that the variable sortname will always be a member of Thing, how can I specify what to sort on?

Some of the things I have tried:

ORDER BY Thing.id // Fail
ORDER BY f.things.id // Fail
ORDER BY things.id // FAIL!

Upvotes: 1

Views: 2384

Answers (1)

JB Nizet
JB Nizet

Reputation: 691745

The query should be:

SELECT DISTINCT thing FROM Foo f 
INNER JOIN f.things thing 
INNER JOIN f.bars bar 
WHERE bar = :obj 
ORDER BY thing.id

i.e. you should use the alias of the entity in the select clause instead of its path, and use the same alias in the order by clause.

Upvotes: 3

Related Questions