Skip to main content
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:
  1. First orderBy - Primary sort
  2. Second orderBy - Secondary sort (when primary is same)
  3. Third orderBy - Tertiary sort (when secondary is also same)

​
Practical Examples

// 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");

​
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

​
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

// NULL sorting may differ between PostgreSQL and MySQL

// PostgreSQL: NULL LAST (default)
.orderBy("users.last_login", "desc")

// To specify explicitly, use raw SQL
.whereRaw("ORDER BY users.last_login DESC NULLS FIRST")

​
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