Grails HQL Order By Examples

Mar 08, 2015 Snippet comments

This post will show how to perform HQL Order By in your Grails applications.

Introduction

It is advisable to have all queries to the database use an order by clause. This is because most relational database do not guarantee any default ordering when there is no order by clause in your statements. When you run the same query on the same data, it may result on different ordering every time you run in.

For example, if we have this SQL statement:

select name from animal

This may return this result on the first run:

Cat
Elephant
Dog

And return this on the second run:

Dog
Cat
Elephant

This is confusing to the user viewing your data.

Examples

Here are some examples on how to use Grails HQL Order By in your code.

Order by primary key example:

Person.executeQuery("from Person order by id")

Order by last name and then by first name example

Person.executeQuery("from Person order by lastName, firstName")

Order with Domain alias example:

Person.executeQuery("from Person p order by p.lastName, p.firstName")

Order with pagination example:

Person.executeQuery("from Person p order by p.lastName, p.firstName", [offset:0, max:10])

Ascending example:

Person.executeQuery("from Person order by id asc")

By default, order is ascending when not specified.

Descending example:

Person.executeQuery("from Person order by id desc")