LogoLogo
9.5.0
9.5.0
  • Introduction
  • What's New?
  • Installation & Usage
  • Migration Guide
  • Contributing & Filing Issues
  • Query Builder
    • Getting a New Query
    • Building Queries
      • Selects
      • From
      • Joins
      • Wheres
      • Order By
      • Group By and Having
      • Limit, Offset, and Pagination
      • Locks
      • Unions
      • Common Table Expressions (i.e. CTEs)
      • Raw Expressions
      • When / Conditionals
      • Query Parameters and Bindings
    • Executing Queries
      • Retrieving Results
      • Aggregates
      • Inserts, Updates, and Deletes
    • Options and Utilities
      • Query Options and Utilities
      • Clone and Reset
      • Return Format
      • Column Formatter
      • Interception Points
    • Debugging
      • sqlCommenter
  • Schema Builder
    • Overview
    • Create
    • Columns
    • Column Modifiers
    • Column Constraints
    • Creating Table Constraints
    • Alter
    • Drop
    • Debugging
  • External Links
    • API Docs
    • Source Code
    • Issue Tracker
Powered by GitBook
On this page
  • Order By (String)
  • Order By (List)
  • Order By (Array of Strings)
  • Order By (Array of Structs)
  • Order By (Subquery)
  • Order By Raw
  • clearOrders
  • reorder

Was this helpful?

Edit on GitHub
Export as PDF
  1. Query Builder
  2. Building Queries

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)

Name

Type

Required

Default

Description

column

any

true

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc".

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
QueryBuilder
query.from( "users" )
    .orderBy( query.raw( "DATE(created_at)" ) );
MySQL
SELECT *
FROM `users`
ORDER BY DATE(created_at)

Order By (List)

Name

Type

Required

Default

Description

column

any

true

The list of the columns to order by. Each column can optionally declare it's sort direction after a pipe delimiter. (e.g. `"height

desc"`).

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc". This value will be used as the default value for all entries in the column list that fail to specify a direction for a specific column.

QueryBuilder
query.from( "users" )
    .orderBy( "email|asc,username", "desc" );
MySQL
SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Strings)

Name

Type

Required

Default

Description

column

any

true

The array of the columns to order by. Each column can optionally declare it's sort direction after a pipe delimiter. (e.g. `"height

desc"`).

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc". This value will be used as the default value for all entries in the column array that fail to specify a direction for a specific column.

QueryBuilder
query.from( "users" )
    .orderBy( [ "email|asc", "username" ], "desc" );
MySQL
SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Structs)

Name

Type

Required

Default

Description

column

any

true

The array of the columns to order by. Each column can optionally declare it's sort direction using a struct. The struct should have a column key and an optional direction key. (e.g. { column = "favorite_color", direction = "desc" }).

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc". This value will be used as the default value for all entries in the column array that fail to specify a direction for a specific column.

QueryBuilder
query.from( "users" )
    .orderBy( [
        { "column": "email", "direction": "asc" },
        "username"
    ], "desc" );
MySQL
SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Subquery)

Name

Type

Required

Default

Description

column

any

true

direction

string

false

"asc"

Ignored when using a Function or QueryBuilder instance.

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

Name

Type

Required

Default

Description

expression

string

true

The raw SQL expression to use.

bindings

array

false

[]

Any bindings (?) used in the expression.

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

Name

Type

Required

Default

Description

No arguments

QueryBuilder
query.from( "users" )
    .orderBy( "email" )
    .clearOrders();
MySQL
SELECT *
FROM `users`

reorder

Name

Type

Required

Default

Description

column

any

true

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc".

QueryBuilder
query.from( "users" )
    .orderBy( "email" )
    .reorder( "username" );
MySQL
SELECT *
FROM `users`
ORDER BY `username` ASC
PreviousWheresNextGroup By and Having

Was this helpful?

The name of the column to order by. An can be passed as well.

You can also provide an .

The name of the column to order by. An can be passed as well. An array can be passed with any combination of simple values, array, struct, or list for each entry in the array (an example with all possible value styles: column = [ "last_name", [ "age", "desc" ], { column = "favorite_color", direction = "desc" }, "height|desc" ];. The column argument can also just accept a comman delimited list with a pipe ( | ) as the secondary delimiter denoting the direction of the order by. The pipe delimiter is also used when parsing the column argument when it is passed as an array and the entry in the array is a pipe delimited string.

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

The name of the column to order by. An can be passed as well.

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

Expression
Quick
orderBy
Quick
Expression
Expression
Expression