LIMIT in Postgres not supported in HQL?

Question

I am having error using the following code in Grails executing HQL to get the first 30 Item objects:

 def items = Item.executeQuery('SELECT i FROM Item as i LIMIT 30 OFFSET 0')

my backend database is Postgres. However, I got:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: LIMIT near line 1, column ...

Obviously, the error tells that LIMIT is not supported by HQL. How do I make it work? In other words, how do I make HQL execute native SQL that is well supported by Postgres?

Answer 1

you could do it the Grails/GORM way using list also

def items = Item.list(offset:0, max:30)

Since you are using HQL I have edited my answer based on comment below; either approach would work

def items = Item.executeQuery('SELECT i FROM Item as i', [max: 30, offset: 0])