OrderBy

Query bean properties have asc() and desc() methods such that they are added to the orderBy clause.

List<Customer> customers =
  new QCustomer()
    .status.in(Status.NEW, Status.ACTIVE)
    .orderBy()
      .name.desc() // order by t0.name desc
    .findList();

Produces the SQL below:

select ...
from customer t0
where t0.status in (?, ?)
order by t0.name desc

We can add multiple properties to the orderBy clause.

List<Customer> customers =
  new QCustomer()
    .status.in(Status.NEW, Status.ACTIVE)
    .orderBy()
      .name.desc()
      .id.asc()
    .findList();

Standard orderBy

For standard queries we use orderBy().desc() or orderBy().asc() which also can be chained. The same query as above is written without query beans as:

List<Customer> customers = database.find(Customer.class)
    .where().in("status"), Status.NEW, Status.ACTIVE)
    .orderBy()
      .desc("name")
      .asc("id")
    .findList();

OrderBy expression

We can specify an order by expression which can include sql formula via orderBy(string).

e.g. simple expression
new QCustomer()
  .orderBy("name asc,status desc")
  .findList()
e.g. expression using DB function
List<Customer> customers =
  new QCustomer()
    .status.in(Status.NEW, Status.ACTIVE)
    .orderBy("coalesce(activeDate, now()) desc, name, id")
    .findList();

Nulls high, Nulls low

The order by expression can include nulls high or nulls low.

List<Customer> customers =
  new QCustomer()
    .orderBy("name nulls high, id desc")
    .findList();

Collate

We can specify the collate to use via asc() and desc().

List<Customer> customers =
  new QCustomer()
    .orderBy()
      .asc("name", "latin_2");
      .desc("id", "latin_1");
    .findList();

ToMany ordering

Note that when an ORM query is executed as multiple sql queries due to the use of maxRows or fetchQuery (refer to fetch rules ) - then the associated orderBy expressions for the toMany relationship is automatically moved to the appropriate sql query.

Example
QContact contact = QContact.alias();

List<Customer> customer =
  new QCustomer()
    .contacts.fetch(contact.firstName, contact.lastName, contact.email)
    .orderBy()
      .name.asc()
      .contacts.email.asc()              // (1) automatically moved to secondary sql query
    .setMaxRows(10)                      // (2) maxRows
    .findList();

The above ORM query is executed as 2 sql queries due to the maxRows - see fetch rules.

-- Primary query
select t0.id, t0.status, t0.name, ...
from customer t0
order by t0.name
limit 10                                -- (2)
-- Secondary query
select t0.customer_id, t0.id, t0.first_name, t0.last_name, t0.email
from contact t0
where (t0.customer_id) in (? )
order by t0.email                       -- (1)