Query Builder and Advanced Eloquent

Laravel's query builder provides a fluent interface for constructing and running database queries. While Eloquent methods cover most cases, the DB facade lets you write raw queries with DB::select(), DB::insert(), DB::update(), and DB::delete(). Raw expressions using DB::raw() can be inserted anywhere a value or column is expected, useful for SQL functions and computed columns. Advanced Eloquent features include subquery selects, subquery joins, and lateral joins. The selectSub() method lets you add a subquery as a column. whereHas() and doesntHave() filter records by the existence of a related record matching conditions, without loading the relation itself. orderByDesc(), latest(), oldest(), and reorder() control query ordering. Chunking records with chunk() or lazy() avoids memory exhaustion when processing large datasets. chunk() fetches records in batches and passes each batch to a closure. lazy() returns a LazyCollection that fetches records one-by-one from a cursor. Upserts are handled by upsert(), which inserts or updates records in a single query. The firstOrCreate() and updateOrCreate() methods are convenient for seed data and import scripts.

Example

<?php
// Advanced Eloquent queries

use App\Models\Post;
use Illuminate\Support\Facades\DB;

// whereHas: posts that have at least one published comment
$posts = Post::whereHas('comments', function ($query) {
    $query->where('approved', true);
})->get();

// withCount: add comments_count column without loading comments
$posts = Post::withCount('comments')->latest()->paginate(10);

// Subquery select: latest comment date per post
$posts = Post::addSelect([
    'latest_comment_at' => \App\Models\Comment::select('created_at')
        ->whereColumn('post_id', 'posts.id')
        ->latest()
        ->limit(1),
])->get();

// Chunking for large datasets
Post::chunk(200, function ($posts) {
    foreach ($posts as $post) {
        // process $post
    }
});

// Upsert multiple rows
Post::upsert([
    ['slug' => 'hello-world', 'title' => 'Hello World', 'user_id' => 1],
    ['slug' => 'second-post', 'title' => 'Second Post', 'user_id' => 1],
], uniqueBy: ['slug'], update: ['title']);

// Raw query via DB facade
$results = DB::select('SELECT id, title FROM posts WHERE status = ?', ['published']);

HTML

Full Editor

Related Resources

Laravel Reference

Complete tag & property list

Laravel How-To Guides

Step-by-step practical guides

Laravel Exercises

Practice what you've learned