Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 67 ยท Eloquent ORM

Querying with Eloquent

Query models with Eloquent: local and global scopes, aggregates, chunking and lazy collections, pagination, withCount and subqueries, with runnable PHP stand-ins.

Reading data is what most pages do all day, so Eloquent's query side deserves a closer look. Every model method you call statically, such as Post::where(...), returns an Eloquent builder. The builder wraps the query builder you already know and adds model features: results come back as model objects, and you can attach named, reusable pieces of query called scopes.

In this lesson you will learn how to name your query fragments with scopes, how to read big tables without running out of memory, how aggregates and pagination work, and how to pull counts and subquery values into each row. As always, the Laravel code is read-only here, because Laravel cannot run on this site. The runnable examples are plain PHP stand-ins that show the mechanism with SQLite. If you need a refresher on models, see Eloquent models and CRUD.

The builder in one minute

Calls on the builder fall into two groups. Constraint methods (where, orderBy, limit, with) return the builder so you can keep chaining. Terminal methods (get, first, find, count, paginate, exists) run SQL and give you something else.

PHP
$posts = Post::query()
    ->where('published', true)
    ->where('views', '>=', 100)
    ->orderByDesc('views')
    ->limit(10)
    ->get();   // Illuminate\Database\Eloquent\Collection of Post models

Nothing hits the database until get(). This is why you can build a query across several functions, or conditionally, with when():

PHP
$posts = Post::query()
    ->when($request->filled('author'), fn ($q) => $q->where('author', $request->author))
    ->get();

Other single-row helpers are first() (or null), firstOrFail() (a 404 when missing), sole() (exactly one row or an exception), and value('title'), which returns one column of the first row.

Local scopes

A local scope is a named query fragment on the model. Without scopes, where('published', true) is copied into twenty controllers, and when "published" later also needs published_at <= now(), you edit twenty places. A scope gives the condition one name and one home.

In Laravel 13 you mark a model method with the #[Scope] attribute. The method receives the builder:

PHP
use Illuminate\Database\Eloquent\Attributes\Scope;
use Illuminate\Database\Eloquent\Builder;

class Post extends Model
{
    #[Scope]
    protected function published(Builder $query): void
    {
        $query->where('published', true);
    }

    #[Scope]
    protected function popular(Builder $query, int $minViews = 100): void
    {
        $query->where('views', '>=', $minViews);
    }
}

$posts = Post::published()->popular(500)->latest()->get();

The older style, a method named scopePopular, still works, but the attribute is the modern form. Scopes are chainable and can be mixed with ordinary constraints. Keep each one small and focused on one idea; a scope that does five things is hard to reuse.

Global scopes

A global scope is applied to every query on a model automatically. Typical uses are soft deletes (covered in model events and soft deletes) and tenant isolation ("only rows of the current team"). You write a class that implements Scope, and attach it with an attribute:

PHP
use Illuminate\Database\Eloquent\Attributes\ScopedBy;
use Illuminate\Database\Eloquent\Scope;

class NotArchivedScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $builder->where($model->getTable().'.archived', false);
    }
}

#[ScopedBy([NotArchivedScope::class])]
class Post extends Model {}

Post::all();                                        // archived rows hidden
Post::withoutGlobalScope(NotArchivedScope::class)->get();   // everything

Global scopes are powerful and invisible, which is also their risk. A developer reading Post::all() cannot see the hidden where. Use them for rules that must never be forgotten, and document them. Note that global scopes apply to queries made through the model; raw DB::table('posts') calls bypass them.

Here is a stand-in that shows both kinds of scope. Local scopes are found through __call, global scopes are applied right before the SQL is built, and withoutGlobalScope() removes one:

PHP A builder with local and global scopes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Notice that the archived row only appears when the global scope is removed, and that the caller never typed archived = 0. That is the whole point, and the whole danger, of global scopes.

Reading big tables: chunk, lazy and cursor

Post::all() loads every row into memory as a model object. For ten thousand rows that may be fine; for ten million it kills the process. Eloquent has three ways to stream:

  • chunk(500, fn ($posts) => ...) runs the query in pages of 500 using LIMIT/OFFSET and hands you each page as a collection.
  • chunkById(500, fn ($posts) => ...) pages by primary key (WHERE id > last LIMIT 500). Use this when your callback updates or deletes the rows you are iterating, because with offsets, changing rows shifts the pages and you skip records.
  • lazy() and lazyById() return a LazyCollection: you write a plain foreach and Eloquent fetches chunks behind the scenes. cursor() instead keeps one query open and hydrates one model at a time, which uses the least memory but holds a database connection open for the whole loop.
PHP
Post::where('published', true)->chunkById(500, function ($posts) {
    foreach ($posts as $post) {
        $post->update(['slug' => Str::slug($post->title)]);
    }
});

foreach (Post::lazyById(1000) as $post) {
    // one model at a time, fetched 1000 rows per query
}

Lazy collections are covered at the end of Collections.

Aggregates and existence checks

The builder has count(), sum('views'), avg('views'), min() and max(). They run one aggregate query and return a number, not models:

PHP
$total   = Post::count();
$views   = Post::where('published', true)->sum('views');
$exists  = Post::where('slug', $slug)->exists();   // true/false, cheaper than count()

Prefer exists() over count() > 0, and prefer database aggregates over loading models into a collection just to add up a column: the database does it in microseconds.

Pagination

Three paginators trade features for cost:

Method Extra query Gives you Best for
paginate(15) a COUNT(*) total, page numbers, last page admin tables, numbered pages
simplePaginate(15) none only next and previous big tables where totals are not needed
cursorPaginate(15) none next and previous by a position marker infinite scroll, feeds, APIs

paginate() reads the ?page= query string automatically and Blade renders links with {{ $posts->links() }}. simplePaginate fetches one extra row to learn whether a next page exists. Cursor pagination uses a WHERE on the ordering column instead of OFFSET, so it stays fast on deep pages and does not skip or repeat rows when new data arrives, but it needs a unique, stable ordering and cannot jump to "page 40".

PHP paginate vs simplePaginate vs chunkById

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The totals show the price of page numbers: paginate spends an extra COUNT(*), simplePaginate does not. Notice also that this stand-in runs one more query than there are chunks, because it stops only when a query returns nothing. Laravel's real chunkById is a little smarter and stops as soon as a chunk comes back smaller than the requested size.

Counts and sums from relations

Often you want "posts, each with its comment count". Loading comments just to count them is wasteful. withCount adds a subquery column instead:

PHP
$posts = Post::withCount('comments')->get();
echo $posts[0]->comments_count;

Post::withSum('orderItems', 'quantity');   // order_items_sum_quantity
Post::withExists('comments');               // comments_exists (bool)
Post::withCount(['comments as approved_comments_count' => fn ($q) => $q->where('approved', true)]);

These need a defined relationship, which you will build in Eloquent relationships, and they are one of the main tools for avoiding the N+1 problem.

Subqueries

When the value you need is not a plain relation count, pass a query to addSelect or orderBy. Eloquent turns it into a subselect:

PHP
$users = User::addSelect([
    'last_login_at' => Login::select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1),
])->orderByDesc('last_login_at')->get();

Each user now has a last_login_at attribute from a single query, with no per-user lookups. Reach for whereColumn to tie the inner query to the outer row. This is an advanced tool; if a subquery grows complicated, a database view or a raw expression with bindings may be clearer. Never concatenate user input into raw SQL; see security in Laravel.

flowchart LR
    A["Post::published()"] --> B["Eloquent Builder"]
    B --> C["local scopes<br/>(your named fragments)"]
    B --> D["global scopes<br/>(applied automatically)"]
    C --> E["Query Builder"]
    D --> E
    E --> F["SQL + bindings"]
    F --> G["Models in a Collection"]

Scopes add constraints to the builder; the SQL is only built and run when you call a terminal method.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You loop over every row with chunk() and your callback deletes each row. What is the safer method?

    With offset paging, deleting rows shifts later rows into earlier pages and some get skipped. chunkById() continues from the last id it saw, so changes do not move the window.
  2. 2. What is the main difference between a local scope and a global scope?

    Local scopes are opt-in named fragments, while global scopes are applied implicitly, which is why they are good for must-never-forget rules and risky for surprising readers.
  3. 3. You need page numbers with a total count. Which paginator fits?

    paginate() runs an extra COUNT(*) to know the total and the last page. The other paginators skip that query and so cannot show page numbers.
  4. 4. Why is withCount('comments') usually better than loading comments and calling count() in PHP?

    The database counts the rows and returns one number per post, so memory use and query count stay small.

Summary

  • The Eloquent builder chains constraint methods and runs SQL only on terminal methods such as get(), first() and paginate().
  • Local scopes (#[Scope] methods) name reusable query fragments; global scopes (#[ScopedBy]) apply to every query on a model and can be removed with withoutGlobalScope().
  • Use chunkById(), lazyById() or cursor() for large tables, and chunkById() when you modify the rows you loop over.
  • Prefer database aggregates and exists() to doing the arithmetic in PHP.
  • paginate() costs a count query, simplePaginate() does not, and cursorPaginate() scales best for feeds.
  • withCount, withSum and subquery addSelect put computed values into each row in one query.

Next: Eloquent Relationships