Eloquent and Queries

Query logic is the easiest thing to spread across a codebase by accident. The same three-clause where chain shows up in two controllers, a Service, and a job; later the definition of "active provider" changes and you have four places to update. This codebase keeps every list query for an entity behind a single trait — scopeFilterBy — so a new filter is a one-line addition, not a hunt-and-replace.

Two query-builder macros — whereByType and whereLikeText — exist for the same reason. They encode the project's conventions for "value-or-array" filters and case-insensitive substring search so the call site reads at the level of intent instead of plumbing.

The rest of the rules below — eager loading, chunking, column projection — are about keeping the database fast. Most performance problems in this codebase trace back to one of three things: an N+1 query, get() on a million-row table, or SELECT * on a wide row. Each has a one-line fix.

The shape

Here is a filter trait, the Model that uses it, and the Action that runs the query.

// src/Domain/Provider/Models/Scopes/ProviderFilter.php
namespace Domain\Provider\Models\Scopes;

use Domain\Provider\DataTransferObjects\FilterProviderData;
use Illuminate\Database\Eloquent\Builder;

trait ProviderFilter
{
    public function scopeFilterBy(Builder $query, ?FilterProviderData $filters): void
    {
        $query->when($filters?->id, fn (Builder $q) => $q->whereByType('id', $filters->id))
            ->when($filters?->name, fn (Builder $q) => $q->whereLikeText('name', $filters->name))
            ->when($filters?->status, fn (Builder $q) => $q->whereByType('status', $filters->status));
    }
}

// src/Domain/Provider/Actions/GetProviders.php
final class GetProviders extends GetDataAbstract
{
    public function query(): Builder
    {
        return Provider::filterBy($this->data);
    }
}

The trait lives in Models/Scopes/, the Model uses it, and the Action calls Provider::filterBy($this->data). Because the filter parameter is nullable, the scope works whether or not a filter DTO is passed.

Centralise filter logic in scopeFilterBy

Every entity Model has a {Entity}Filter trait under src/Domain/{Domain}/Models/Scopes/. The trait exposes exactly one method: scopeFilterBy(Builder $query, ?Filter{Entity}Data $filters): void. Every list query for that entity — including the Action that backs the public Get{Entity}s — routes through this scope.

The scope always accepts ?Filter{Entity}Data (nullable). When the caller passes nothing, every when() short-circuits and the scope yields the unfiltered base query. The Action stays a single code path whether filters are present or not.

✅ Good — Controller builds the DTO, the Action runs the scope.

$filters = FilterProviderData::fromArray($request->all());

return app(ProviderContract::class)->getProviders($filters)->pagination();

❌ Bad — filter logic spread across the Controller and the Service.

$query = Provider::query();
if ($request->status) {
    $query->where('status', $request->status);
}
if ($request->city_id) {
    $query->where('city_id', $request->city_id);
}
return $query->paginate();

The second version puts the rules for "what counts as a valid filter" in the Controller — which then has to be repeated in every other Controller that lists Providers. Once the scope exists, every list endpoint reads the same.

The whereByType macro

Use whereByType($column, $value) for any filter where the value can be a single scalar, an array of scalars, or null. The macro switches between where and whereIn, and short-circuits on null. This is the standard for ID filters, status filters, and any column where the caller might pass one value or many.

✅ Good — one call handles scalar, array, and null.

$query->when($filters?->status, fn ($q) => $q->whereByType('status', $filters->status));

❌ Bad — branching by hand.

if (is_array($filters->status)) {
    $query->whereIn('status', $filters->status);
} elseif ($filters->status !== null) {
    $query->where('status', $filters->status);
}

Sidestepping the macro produces inconsistent behaviour the next time the column starts accepting arrays. Use it everywhere the value type is flexible — including inside scopeFilterBy.

The whereLikeText macro

Use whereLikeText($column, $term) for case-insensitive substring search. The macro escapes % and _ from the user input, normalises the case, and wraps the term with % on both sides. Hand-rolling the LIKE clause leaks SQL wildcards into user-controlled input and produces locale-dependent matches.

✅ Good — escaped, normalised, consistent.

$query->when($filters?->email, fn ($q) => $q->whereLikeText('email', $filters->email));

❌ Bad — user input can contain % or _, behaviour is locale-dependent.

$query->when($filters?->email, fn ($q) => $q->where('email', 'LIKE', "%{$filters->email}%"));

The raw version lets a user type % and match every row. Apply the macro inside scopeFilterBy too — never inline a raw LIKE even there.

Eager loading

Load relationships with with() and counts with withCount() on the same builder that runs the query. Never let Blade trigger a query — that is the N+1 hot path.

✅ Good — one extra query each.

$orders = Order::with('provider')->withCount('lines')->get();
foreach ($orders as $order) {
    echo $order->provider->name;       // already loaded
    echo $order->lines_count;          // already counted
}

❌ Bad — N+1.

$orders = Order::all();
foreach ($orders as $order) {
    echo $order->provider->name;       // one query per order
    echo $order->lines->count();       // and another
}

The bad version sends 1 + 2N queries — fine with three orders, ruinous with three hundred.

Get{Entity}s Actions accept $withRelations and $withCount arrays so the caller declares what it needs without bloating the default query:

$providers = app(ProviderContract::class)
    ->getProviders($filters, withRelations: ['city', 'manager'], withCount: ['orders'])
    ->pagination();

Chunking large result sets

For more than ~1 000 rows, do not call get() — you will load the whole set into memory. Use chunk() or chunkById() for batched processing, and cursor() for memory-efficient read-only iteration.

✅ Good — chunk for processing, commits one batch at a time.

Order::filterBy($filters)
    ->chunkById(500, function ($orders) {
        foreach ($orders as $order) {
            RecalculateOrderTotal::handle($order);
        }
    });

✅ Good — cursor for read-only streaming (exports, reports).

foreach (Order::filterBy($filters)->cursor() as $order) {
    yield OrderExportRow::fromModel($order);
}

Prefer chunkById() over chunk() when the loop mutates the rows it iterates. chunk() paginates by OFFSET, so an update that changes the sort order can skip or revisit rows.

Selecting only needed columns

Avoid SELECT * when you only need a handful of columns. Wide rows — anything with TEXT or JSON columns — inflate memory and serialisation time. Pass an explicit column list when the caller doesn't need the full row.

✅ Good — only the two columns hit the DB.

$providers = Provider::filterBy($filters)->select(['id', 'name'])->get();

❌ Bad — pulls every column even though only id and name are used.

$providers = Provider::filterBy($filters)->get();
return $providers->map(fn ($p) => ['id' => $p->id, 'label' => $p->name]);

For dropdowns and autocomplete endpoints, always project — these endpoints are hot, the payload should be lean.

When projecting, always include the primary key. Eloquent needs id to hydrate relationships and to power findOrFail semantics downstream.

// ❌ Bad — Eloquent will lazy-reload the row to get the primary key
Provider::select(['name'])->get();

// ✅ Good — id is always in the projection
Provider::select(['id', 'name'])->get();

Eloquent relationships

Declare relationships on the Model — belongsTo, hasMany, belongsToMany, morphTo, morphMany. Always type the return so static analysis catches misuse.

// src/Domain/Order/Models/Entities/Order.php
class Order extends Model
{
    public function provider(): BelongsTo
    {
        return $this->belongsTo(Provider::class);
    }

    public function lines(): HasMany
    {
        return $this->hasMany(OrderLine::class);
    }

    public function attachments(): MorphMany
    {
        return $this->morphMany(Attachment::class, 'attachable');
    }
}

Follow Laravel's foreign-key convention (provider_id, order_id). Don't override key names unless the column already exists with a non-standard name.

For cross-domain reads, do not call another domain's relationship directly from outside that domain. Go through the Contract — see domain boundaries.

What to read next

• DTOs — the Filter{Entity}Data shape that drives every scope.
• Actions — where Get{Entity}s and GetDataAbstract are defined.
• Migrations — how column definitions interact with index decisions.
• API responses — whenLoaded() guards inside Resources.
• Anti-patterns — items #12, #13, and #14 cover the query mistakes the reviewer flags most often.