orderBy - 🌲 Sonamu

orderBy is a method for sorting query results by specific columns. You can sort in ascending (ASC) or descending (DESC) order, and can sort by multiple columns.

Basic Usage

Ascending (ASC)

const users = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name" })
  .orderBy("users.name", "asc");

// ORDER BY users.name ASC

Descending (DESC)

const posts = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title", created_at: "posts.created_at" })
  .orderBy("posts.created_at", "desc");

// ORDER BY posts.created_at DESC

Multiple Column Sorting

You can sort by multiple columns by chaining orderBy.

const users = await puri
  .table("users")
  .select({ id: "users.id", role: "users.role", name: "users.name" })
  .orderBy("users.role", "asc") // First: role ascending
  .orderBy("users.name", "asc"); // Second: name ascending

// ORDER BY users.role ASC, users.name ASC

Sort Priority:

First orderBy - Primary sort
Second orderBy - Secondary sort (when primary is same)
Third orderBy - Tertiary sort (when secondary is also same)

NULL Sort Order (nulls)

The third argument nulls specifies where NULL values should be placed in the sort order.

// NULL at the end
const users = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name", last_login: "users.last_login" })
  .orderBy("users.last_login", "desc", "last");

// ORDER BY users.last_login DESC NULLS LAST

// NULL first
const posts = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title", deleted_at: "posts.deleted_at" })
  .orderBy("posts.deleted_at", "asc", "first");

// ORDER BY posts.deleted_at ASC NULLS FIRST

nulls options:

Value	Description
`"first"`	Place NULL values at the beginning of results
`"last"`	Place NULL values at the end of results
omitted	Database default behavior (PostgreSQL: ASC → NULLS LAST, DESC → NULLS FIRST)

Array-style orderBy

You can pass multiple sort conditions as an array in a single call.

const products = await puri
  .table("products")
  .select({
    id: "products.id",
    name: "products.name",
    price: "products.price",
    rating: "products.rating",
  })
  .orderBy([
    { column: "products.rating", order: "desc", nulls: "last" },
    { column: "products.price", order: "asc" },
  ]);

// ORDER BY products.rating DESC NULLS LAST, products.price ASC

Each array item can be one of the following:

string: Column name only (defaults to ASC)
SqlExpression: SQL expression
object: { column, order?, nulls? } for detailed specification

// Mix different forms
.orderBy([
  "products.featured",                                         // string: ASC
  { column: "products.rating", order: "desc" },                // object: DESC
  { column: "products.price", order: "asc", nulls: "last" },  // object + nulls
])

Practical Examples

Latest First
Alphabetical
Complex Sorting
Numeric Sorting
NULL Handling

// Latest posts first
const posts = await puri.table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    created_at: "posts.created_at"
  })
  .orderBy("posts.created_at", "desc")
  .limit(20);

// Most recently updated first
const users = await puri.table("users")
  .select({
    id: "users.id",
    name: "users.name",
    updated_at: "users.updated_at"
  })
  .orderBy("users.updated_at", "desc");

// Names in alphabetical order
const users = await puri.table("users")
  .select({
    id: "users.id",
    name: "users.name"
  })
  .orderBy("users.name", "asc");

// Titles in reverse alphabetical order
const products = await puri.table("products")
  .select({
    id: "products.id",
    name: "products.name"
  })
  .orderBy("products.name", "desc");

// Priority: status → created date
const orders = await puri.table("orders")
  .select({
    id: "orders.id",
    status: "orders.status",
    created_at: "orders.created_at"
  })
  .orderBy("orders.status", "asc")        // pending → processing → completed
  .orderBy("orders.created_at", "desc");  // Latest first

// Priority: featured → rating → sales
const products = await puri.table("products")
  .select({
    id: "products.id",
    name: "products.name",
    featured: "products.featured",
    rating: "products.rating",
    sales: "products.sales"
  })
  .orderBy("products.featured", "desc")  // Featured first
  .orderBy("products.rating", "desc")    // High ratings
  .orderBy("products.sales", "desc");    // High sales

// Lowest price first
const products = await puri.table("products")
  .select({
    id: "products.id",
    name: "products.name",
    price: "products.price"
  })
  .where("products.published", true)
  .orderBy("products.price", "asc");

// Highest views first
const posts = await puri.table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    views: "posts.views"
  })
  .orderBy("posts.views", "desc")
  .limit(10);

// Use the nulls option to explicitly control NULL placement
const users = await puri.table("users")
  .select({
    id: "users.id",
    name: "users.name",
    last_login: "users.last_login"
  })
  .orderBy("users.last_login", "desc", "last");  // NULL at the end

// NULL first
const posts = await puri.table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    deleted_at: "posts.deleted_at"
  })
  .orderBy("posts.deleted_at", "asc", "first");  // NULL comes first

Sorting Joined Tables

You can also sort by columns from joined tables.

const posts = await puri
  .table("posts")
  .join("users", "posts.user_id", "users.id")
  .select({
    post_id: "posts.id",
    title: "posts.title",
    author_name: "users.name",
  })
  .orderBy("users.name", "asc") // By author name
  .orderBy("posts.created_at", "desc"); // Latest first for same author

// ORDER BY users.name ASC, posts.created_at DESC

Sorting by SELECT Alias

You can also sort by aliases specified in SELECT.

const posts = await puri
  .table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    view_count: "posts.views", // Alias specified
  })
  .orderBy("view_count", "desc"); // Using alias

// SELECT posts.views as view_count
// ORDER BY view_count DESC

Sorting Aggregate Functions

You can sort by aggregate function results with GROUP BY.

const userStats = await puri
  .table("posts")
  .select({
    user_id: "posts.user_id",
    post_count: Puri.count(),
  })
  .groupBy("posts.user_id")
  .orderBy("post_count", "desc") // Most posts first
  .limit(10);

// SELECT user_id, COUNT(*) as post_count
// FROM posts
// GROUP BY user_id
// ORDER BY post_count DESC
// LIMIT 10

Sorting by SQL Expressions

orderBy accepts SqlExpression<"number"> or SqlExpression<"string"> instead of a column name. This is useful when sorting by computed values such as full-text search rankings or similarity scores.

Sorting by Search Rank

// Sort full-text search results by relevance using ts_rank
const posts = await puri
  .table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    rank: Puri.tsRank("posts.content_tsv", "typescript"),
  })
  .orderBy(Puri.tsRank("posts.content_tsv", "typescript"), "desc");

// ORDER BY ts_rank(posts.content_tsv, plainto_tsquery('simple', ?)) DESC

Sorting by Similarity Score

// Sort by pg_trgm similarity score
const users = await puri
  .table("users")
  .select({
    id: "users.id",
    name: "users.name",
    score: Puri.similarity("users.name", "John"),
  })
  .orderBy(Puri.similarity("users.name", "John"), "desc");

// ORDER BY similarity(users.name, ?) DESC

Custom Sorting with rawNumber

// Sort by a custom SQL expression
const products = await puri
  .table("products")
  .select({
    id: "products.id",
    name: "products.name",
    weighted: Puri.rawNumber("(products.views * 0.3 + products.likes * 0.7)"),
  })
  .orderBy(Puri.rawNumber("(products.views * 0.3 + products.likes * 0.7)"), "desc");

// ORDER BY (products.views * 0.3 + products.likes * 0.7) DESC

When using SQL expressions, orderByRaw is used internally and parameter bindings are automatically applied.

Sort Directions

ASC (Ascending)

Numbers: small → large
Strings: A → Z
Dates: past → future
NULL: last

.orderBy("users.age", "asc")
// 18, 25, 30, 35, 40, NULL

DESC (Descending)

Numbers: large → small
Strings: Z → A
Dates: future → past
NULL: last

.orderBy("users.age", "desc")
// 40, 35, 30, 25, 18, NULL

Default is ASC. If direction is not specified, it sorts in ascending order.

Performance Optimization

1. Use Indexes

// ✅ Good: Sort by indexed column
.orderBy("users.created_at", "desc")  // Index needed on created_at

// ❌ Bad: Sort by non-indexed column (slow)
.orderBy("users.bio", "asc")

Recommended Indexes:

CREATE INDEX idx_users_created_at ON users(created_at DESC);
CREATE INDEX idx_posts_published_created ON posts(published, created_at DESC);

2. Use with LIMIT

// ✅ Good: Sort only top N
.orderBy("posts.views", "desc")
.limit(10)  // Only top 10 instead of sorting all

// ⚠️ Caution: Without LIMIT, sorts all rows (slow)
.orderBy("posts.views", "desc")

3. Composite Indexes

When sorting by multiple columns, composite indexes are needed.

// For this query
.orderBy("products.category", "asc")
.orderBy("products.price", "asc")

// This index is needed
CREATE INDEX idx_products_category_price ON products(category, price);

Cautions

1. Sort Order Matters

// Order of orderBy matters

// ✅ Correct: status → date
.orderBy("orders.status", "asc")
.orderBy("orders.created_at", "desc")

// ❌ Different from intention: date → status
.orderBy("orders.created_at", "desc")
.orderBy("orders.status", "asc")

2. NULL Values

// PostgreSQL: NULLS LAST is the default
.orderBy("users.last_login", "desc")

// Use the nulls option to specify explicitly
.orderBy("users.last_login", "desc", "first")  // NULLS FIRST
.orderBy("users.last_login", "desc", "last")   // NULLS LAST

3. Case Sensitivity

// ⚠️ Caution: Case-sensitive sorting
.orderBy("users.name", "asc")
// "Alice", "Bob", "alice", "bob"

// To ignore case
.orderBy(puri.raw("LOWER(users.name)"), "asc")
// "Alice", "alice", "Bob", "bob"

4. Performance Impact

// ❌ Bad: Sort entire large dataset
const allUsers = await puri.table("users").orderBy("users.created_at", "desc"); // Sort millions of rows

// ✅ Good: Limit with LIMIT
const recentUsers = await puri.table("users").orderBy("users.created_at", "desc").limit(100); // Only top 100

Type Safety

orderBy validates columns in a type-safe manner.

// ✅ Valid column
await puri.table("users").orderBy("users.created_at", "desc");

// ❌ Type error: Non-existent column
await puri.table("users").orderBy("users.unknown_field", "desc");

// ❌ Type error: Invalid direction
await puri.table("users").orderBy("users.name", "invalid"); // Only "asc" or "desc" allowed

Next Steps

limit

Limit result count

where

Filter with conditions

select

Select fields to query

join

Join tables

​Basic Usage

​Ascending (ASC)

​Descending (DESC)

​Multiple Column Sorting

​NULL Sort Order (nulls)

​Array-style orderBy

​Practical Examples

​Sorting Joined Tables

​Sorting by SELECT Alias

​Sorting Aggregate Functions

​Sorting by SQL Expressions

​Sorting by Search Rank

​Sorting by Similarity Score

​Custom Sorting with rawNumber

​Sort Directions

​ASC (Ascending)

​DESC (Descending)

​Performance Optimization

​1. Use Indexes

​2. Use with LIMIT

​3. Composite Indexes

​Cautions

​1. Sort Order Matters

​2. NULL Values

​3. Case Sensitivity

​4. Performance Impact

​Type Safety

​Next Steps

limit

where

select

join

Basic Usage

Ascending (ASC)

Descending (DESC)

Multiple Column Sorting

NULL Sort Order (nulls)

Array-style orderBy

Practical Examples

Sorting Joined Tables

Sorting by SELECT Alias

Sorting Aggregate Functions

Sorting by SQL Expressions

Sorting by Search Rank

Sorting by Similarity Score

Custom Sorting with rawNumber

Sort Directions

ASC (Ascending)

DESC (Descending)

Performance Optimization

1. Use Indexes

2. Use with LIMIT

3. Composite Indexes

Cautions

1. Sort Order Matters

2. NULL Values

3. Case Sensitivity

4. Performance Impact

Type Safety

Next Steps