0

orderBy(), orderByRaw(), latest(), oldest()

Beginner5 min read·lv-13-008
sql

Concept

Ordering controls the sequence of results. Correct ordering is critical for pagination (consistent results across pages), user experience (newest first, alphabetical), and database performance (using indexes).

orderBy(string $column, string $direction = 'asc'): direction is 'asc' or 'desc'.

orderByDesc(string $column): Shorthand for orderBy($column, 'desc').

latest(string $column = 'created_at'): Shorthand for orderByDesc('created_at'). Eloquent-only.

oldest(string $column = 'created_at'): Shorthand for orderBy('created_at'). Eloquent-only.

orderByRaw(string $sql): Raw ORDER BY expression for complex ordering:

  • Custom sort order: FIELD(status, 'pending', 'processing', 'completed').
  • NULL last: CASE WHEN column IS NULL THEN 1 ELSE 0 END, column ASC.
  • Relevance scoring from full-text search.

Multiple order by: Chain multiple orderBy() calls. Columns are applied in order: ->orderBy('status')->orderBy('created_at', 'desc').

inRandomOrder(): ORDER BY RAND() — random results. Avoid on large tables (MySQL must sort all rows randomly). Use DB::raw('NEWID()') on SQL Server.

reorder(): Remove all existing order by clauses.

Index usage: An ORDER BY on an indexed column is free (the index is already ordered). On an unindexed column, MySQL must sort the full result set in memory (filesort). Check with EXPLAIN SELECT ....

Code Example

php
<?php
use Illuminate\Support\Facades\DB;

// Basic ordering
DB::table('users')->orderBy('name')->get();                   // ASC
DB::table('users')->orderBy('created_at', 'desc')->get();    // DESC
DB::table('users')->orderByDesc('created_at')->get();        // shorthand

// Eloquent shorthands
User::latest()->get();        // ORDER BY created_at DESC
User::oldest()->get();        // ORDER BY created_at ASC
User::latest('published_at')->get(); // ORDER BY published_at DESC

// Multiple order by — status first, then date within each status
DB::table('orders')
    ->orderBy('status')
    ->orderByDesc('created_at')
    ->get();

// Custom sort order (MySQL FIELD)
DB::table('orders')
    ->orderByRaw("FIELD(status, 'urgent', 'pending', 'processing', 'completed', 'cancelled')")
    ->get();

// NULLs last — show featured posts first, then regular
DB::table('posts')
    ->orderByRaw('CASE WHEN featured_at IS NULL THEN 1 ELSE 0 END, featured_at DESC')
    ->get();

// Relevance-based ordering for search
$term = 'laravel routing';
DB::table('posts')
    ->selectRaw('*, MATCH(title, body) AGAINST(? IN NATURAL LANGUAGE MODE) as relevance', [$term])
    ->whereRaw('MATCH(title, body) AGAINST(? IN NATURAL LANGUAGE MODE)', [$term])
    ->orderByDesc('relevance')
    ->get();

// Random order (small tables only)
DB::table('testimonials')->inRandomOrder()->limit(3)->get();

// Remove existing ordering (when building queries incrementally)
$query = User::orderBy('name');
if ($needsRandomOrder) {
    $query->reorder()->inRandomOrder();
}
PreviousgroupBy(), having(), havingRaw()Query BuilderNextPagination — paginate(), simplePaginate(), cursorPaginate()Query Builder