Advanced Query Building - Laravel Eloquent ORM

Introduction

Eloquent is built on Laravel's Query Builder, so every where, whereIn, join, orderBy, groupBy, scope, and raw expression is available alongside model instantiation.

Key Concepts

where clause: The basic filter — where('column', 'operator', 'value') or the shortcut where('column', 'value').
whereIn / whereBetween / whereNull: Specialized filters for common patterns.
Pagination (paginate, simplePaginate, cursorPaginate): Three strategies for slicing result sets with different performance tradeoffs.
Local scope: A reusable query constraint defined on the model as scopePublished($query), called as Post::published().
Global scope: A constraint automatically applied to every query for a model (e.g., soft deletes).

Real World Context

Beyond basic CRUD, real applications have filters, sorts, aggregates, and joins. The query builder is where those needs get expressed — and scopes are how you keep the same 3-line condition out of 20 controllers.

Deep Dive

Eloquent is built on Laravel's Query Builder, giving you access to powerful query capabilities while working with models.

Where Clauses

Basic Where

php
$posts = Post::where('status', 'published')->get();
$posts = Post::where('status', '=', 'published')->get();  // Same

// Different operators
$posts = Post::where('views', '>', 100)->get();
$posts = Post::where('views', '>=', 100)->get();
$posts = Post::where('views', '<>', 0)->get();  // Not equal
$posts = Post::where('title', 'like', '%Laravel%')->get();

Multiple Conditions

php
// AND conditions
$posts = Post::where('status', 'published')
    ->where('category_id', 1)
    ->get();

// Or as array
$posts = Post::where([
    ['status', 'published'],
    ['category_id', 1],
])->get();

// OR conditions
$posts = Post::where('status', 'published')
    ->orWhere('featured', true)
    ->get();

// Grouped conditions (parentheses)
$posts = Post::where('status', 'published')
    ->where(function ($query) {
        $query->where('views', '>', 1000)
              ->orWhere('featured', true);
    })
    ->get();
// WHERE status = 'published' AND (views > 1000 OR featured = true)

Advanced Where Clauses

php
// whereIn / whereNotIn
$posts = Post::whereIn('category_id', [1, 2, 3])->get();
$posts = Post::whereNotIn('status', ['draft', 'archived'])->get();

// whereBetween
$posts = Post::whereBetween('views', [100, 1000])->get();
$posts = Post::whereNotBetween('created_at', [$start, $end])->get();

// whereNull / whereNotNull
$posts = Post::whereNull('published_at')->get();
$posts = Post::whereNotNull('published_at')->get();

// whereDate / whereMonth / whereYear
$posts = Post::whereDate('created_at', '2024-01-15')->get();
$posts = Post::whereMonth('created_at', 12)->get();
$posts = Post::whereYear('created_at', 2024)->get();

// whereColumn (compare two columns)
$posts = Post::whereColumn('created_at', 'updated_at')->get();
$posts = Post::whereColumn('views', '>', 'comments_count')->get();

Ordering and Pagination

php
// Order by
$posts = Post::orderBy('created_at', 'desc')->get();
$posts = Post::latest()->get();  // Same as orderBy('created_at', 'desc')
$posts = Post::oldest()->get();  // orderBy('created_at', 'asc')
$posts = Post::inRandomOrder()->get();

// Multiple ordering
$posts = Post::orderBy('featured', 'desc')
    ->orderBy('created_at', 'desc')
    ->get();

// Pagination
$posts = Post::paginate(15);  // 15 per page
$posts = Post::simplePaginate(15);  // Simpler, no total count
$posts = Post::cursorPaginate(15);  // For large datasets

// In Blade
{{ $posts->links() }}  // Pagination links

Selecting Specific Columns

php
// Select specific columns
$posts = Post::select('title', 'slug', 'published_at')->get();

// Add columns
$posts = Post::select('title')
    ->addSelect('body')
    ->get();

// Distinct
$categories = Post::distinct()->pluck('category_id');

Aggregates and Grouping

php
// Aggregates
$count = Post::where('published', true)->count();
$maxViews = Post::max('views');
$avgRating = Post::avg('rating');
$totalViews = Post::sum('views');

// Group by
$postsByCategory = Post::select('category_id', DB::raw('COUNT(*) as count'))
    ->groupBy('category_id')
    ->get();

// Having
$popularCategories = Post::select('category_id', DB::raw('COUNT(*) as count'))
    ->groupBy('category_id')
    ->having('count', '>', 10)
    ->get();

Joins

php
// Inner join
$posts = Post::join('users', 'posts.user_id', '=', 'users.id')
    ->select('posts.*', 'users.name as author_name')
    ->get();

// Left join
$posts = Post::leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->select('posts.*', DB::raw('COUNT(comments.id) as comments_count'))
    ->groupBy('posts.id')
    ->get();

Query Scopes

Reusable query constraints. Laravel 11+ supports two styles — the legacy scopeXxx() convention and the modern #[Scope] attribute. Both work in Laravel 13; the attribute style is the idiomatic modern default because the method name matches how you call it.

php
use Illuminate\Database\Eloquent\Attributes\Scope;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    /** Modern style: method name matches the call site. */
    #[Scope]
    protected function published(Builder $query): void
    {
        $query->whereNotNull('published_at')
            ->where('published_at', '<=', now());
    }

    #[Scope]
    protected function featured(Builder $query): void
    {
        $query->where('featured', true);
    }

    #[Scope]
    protected function ofCategory(Builder $query, int $categoryId): void
    {
        $query->where('category_id', $categoryId);
    }

    /** Legacy style — the scope prefix is stripped at the call site. */
    public function scopeRecent(Builder $query): void
    {
        $query->where('created_at', '>=', now()->subDays(7));
    }
}

Usage:

php
$posts = Post::published()->get();
$posts = Post::published()->featured()->get();
$posts = Post::published()->ofCategory(1)->latest()->get();

Global Scopes

Automatically applied to all queries. Laravel 11+ provides the #[ScopedBy] attribute to attach a scope class directly to the model — no booted() boilerplate. The classic addGlobalScope form still works and is handy for inline closures.

php
// app/Models/Scopes/PublishedScope.php
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class PublishedScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $builder->whereNotNull('published_at');
    }
}

php
// app/Models/Post.php
use Illuminate\Database\Eloquent\Attributes\ScopedBy;
use App\Models\Scopes\PublishedScope;

#[ScopedBy(PublishedScope::class)]
class Post extends Model
{
    // ...
}

// Remove for a single query
Post::withoutGlobalScope(PublishedScope::class)->get();

Raw Expressions

php
// Raw select
$posts = Post::select(DB::raw('YEAR(created_at) as year, COUNT(*) as count'))
    ->groupBy('year')
    ->get();

// Raw where
$posts = Post::whereRaw('views > comments_count * 10')->get();

// Raw order
$posts = Post::orderByRaw('FIELD(status, "featured", "published", "draft")')->get();

Common Pitfalls

N+1 from forgetting eager loading — $user->posts inside a loop queries once per iteration.
Not using scopes for reusable conditions — Copy-pasting where('published', true)->where('published_at', '<=', now()) into every controller.
Raw SQL that bypasses model events — DB::update(...) skips observers, dirty tracking, and cast logic.

Best Practices

Extract reusable filters into scopes — Post::published()->featured() reads better than the raw clauses and stays consistent across the app.
Always paginate large result sets — Returning 10,000 rows to a view is slow for everyone.
Use whereHas for relationship filters — User::whereHas('posts', fn ($q) => $q->where('published', true)) is the idiomatic 'users who have published posts'.

Summary

Where clauses support every SQL operator via where('col', 'op', 'val') or shortcut form.
Specialized helpers: whereIn, whereBetween, whereNull, whereDate, whereColumn.
Pagination comes in three flavors: paginate (offset, with total), simplePaginate (offset, no total), cursorPaginate (cursor-based, scales to millions).
Local scopes encapsulate reusable conditions on the model.
Global scopes apply constraints to every query automatically.