1 of 1

Order By

The orderBy method seems simple but has a lot of depth depending on the type of arguments you pass in.

Calling orderBy multiple times appends to the order list.

Order By (String)

QueryBuilder

query.from( "users" )
    .orderBy( "email" );

MySQL

SELECT *
FROM `users`
ORDER BY `email` ASC

Calling orderBy multiple times will append to the order list.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .orderBy( "username", "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

You can also provide an Expression.

QueryBuilder

query.from( "users" )
    .orderBy( query.raw( "DATE(created_at)" ) );

MySQL

SELECT *
FROM `users`
ORDER BY DATE(created_at)

Order By (List)

QueryBuilder

query.from( "users" )
    .orderBy( "email|asc,username", "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Strings)

QueryBuilder

query.from( "users" )
    .orderBy( [ "email|asc", "username" ], "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Structs)

QueryBuilder

query.from( "users" )
    .orderBy( [
        { "column": "email", "direction": "asc" },
        "username"
    ], "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Subquery)

You can order with a subquery using either a function or a QueryBuilder instance.

QueryBuilder

query.from( "users" )
    .orderBy( function( q ) {
        q.selectRaw( "MAX(created_date)" )
            .from( "logins" )
            .whereColumn( "users.id", "logins.user_id" );
    } );

MySQL

SELECT *
FROM `users`
ORDER BY (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
)

Order By Raw

QueryBuilder

query.from( "users" )
    .orderByRaw( "CASE WHEN status = ? THEN 1 ELSE 0 END DESC", [ 1 ] );

MySQL

SELECT *
FROM `users`
ORDER BY CASE WHEN status = ? THEN 1 ELSE 0 END DESC

clearOrders

Clears the currently configured orders for the query. Usually used by downstream libraries like Quick.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .clearOrders();

MySQL

SELECT *
FROM `users`

reorder

Clears the currently configured orders for the query and sets the new orders passed in. Any valid argument to orderBy can be passed here. Usually used by downstream libraries like Quick.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .reorder( "username" );

MySQL

SELECT *
FROM `users`
ORDER BY `username` ASC

Order By

The orderBy method seems simple but has a lot of depth depending on the type of arguments you pass in.

Calling orderBy multiple times appends to the order list.

Order By (String)

QueryBuilder

query.from( "users" )
    .orderBy( "email" );

MySQL

SELECT *
FROM `users`
ORDER BY `email` ASC

Calling orderBy multiple times will append to the order list.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .orderBy( "username", "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

You can also provide an Expression.

QueryBuilder

query.from( "users" )
    .orderBy( query.raw( "DATE(created_at)" ) );

MySQL

SELECT *
FROM `users`
ORDER BY DATE(created_at)

Order By (List)

QueryBuilder

query.from( "users" )
    .orderBy( "email|asc,username", "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Strings)

QueryBuilder

query.from( "users" )
    .orderBy( [ "email|asc", "username" ], "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Structs)

QueryBuilder

query.from( "users" )
    .orderBy( [
        { "column": "email", "direction": "asc" },
        "username"
    ], "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Subquery)

You can order with a subquery using either a function or a QueryBuilder instance.

QueryBuilder

query.from( "users" )
    .orderBy( function( q ) {
        q.selectRaw( "MAX(created_date)" )
            .from( "logins" )
            .whereColumn( "users.id", "logins.user_id" );
    } );

MySQL

SELECT *
FROM `users`
ORDER BY (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
)

Order By Raw

QueryBuilder

query.from( "users" )
    .orderByRaw( "CASE WHEN status = ? THEN 1 ELSE 0 END DESC", [ 1 ] );

MySQL

SELECT *
FROM `users`
ORDER BY CASE WHEN status = ? THEN 1 ELSE 0 END DESC

clearOrders

Clears the currently configured orders for the query. Usually used by downstream libraries like Quick.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .clearOrders();

MySQL

SELECT *
FROM `users`

reorder

Clears the currently configured orders for the query and sets the new orders passed in. Any valid argument to orderBy can be passed here. Usually used by downstream libraries like Quick.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .reorder( "username" );

MySQL

SELECT *
FROM `users`
ORDER BY `username` ASC