The query builder

Eloquent and the DB facade build SQL queries with a chain of methods. No hand-written SQL is needed, and values are inserted safely — through prepared statements. Every method in this lesson works both on models and on DB::table().

Conditions

The where() method takes a column, an operator and a value. The = operator can be omitted:

use App\Models\Post;
 
$published = Post::where('is_published', true)->get();
$popular = Post::where('views', '>', 1000)->get();

Several where() calls in a row are joined with "AND". For "OR" there is orWhere():

$posts = Post::where('is_published', true)
    ->where('views', '>', 100)
    ->orWhere('is_pinned', true)
    ->get();

Common conditions

Method	What it selects
`whereIn('id', [1, 2, 3])`	A value from a list
`whereNull('deleted_at')`	An empty value
`whereBetween('price', [100, 500])`	A value within a range
`whereDate('created_at', '2025-01-30')`	Records for a date
`where('title', 'like', '%tea%')`	A search by part of a string

Grouping conditions

To add parentheses, pass the conditions in a function:

$posts = Post::where('is_published', true)
    ->where(function ($query) {
        $query->where('views', '>', 1000)
            ->orWhere('is_pinned', true);
    })
    ->get();

The result is is_published AND (views > 1000 OR is_pinned).

Sorting and limiting

$latest = Post::orderBy('created_at', 'desc')->limit(5)->get();
 
$latest = Post::latest()->take(5)->get();

latest() is shorthand for sorting by the creation date.

Selecting columns and a single value

$posts = Post::select('id', 'title')->get();
 
$titles = Post::pluck('title');
$byId = Post::pluck('title', 'id');
 
$title = Post::where('id', 1)->value('title');

pluck() returns a collection of one column's values, value() a single value.

Aggregates

$count = Post::where('is_published', true)->count();
$max = Post::max('views');
$average = Post::avg('views');
$exists = Post::where('slug', 'hello')->exists();

exists() is faster than count() > 0: the database stops at the first row found.

A condition when needed

when() adds a condition only if the value is set — handy for filters from a form:

$posts = Post::query()
    ->when($request->category, fn ($query, $category) => $query->where('category_id', $category))
    ->when($request->search, fn ($query, $search) => $query->where('title', 'like', "%{$search}%"))
    ->latest()
    ->get();

Queries without a model

The DB facade works with a table directly and returns plain objects:

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')
    ->join('posts', 'posts.user_id', '=', 'users.id')
    ->select('users.name', DB::raw('COUNT(posts.id) as posts_count'))
    ->groupBy('users.id', 'users.name')
    ->get();

Common mistake

A chain of methods only describes the query. It runs at the last step — get(), first(), count(). Without it the variable holds a query builder, not data:

$posts = Post::where('is_published', true);        // no query has run yet
$posts = Post::where('is_published', true)->get(); // a collection of posts

Thanks for reading!