Eloquent Without the Magic: Custom Relations and Query Builder Internals

Why Bother Going Deeper?

Eloquent's built-in relations cover 90 % of use-cases elegantly. The remaining 10 % — lateral joins, filtered aggregates, multi-column foreign keys — either get shoved into raw SQL strings or balloon into unmaintainable query scopes. Understanding the internals lets you write a proper Relation subclass instead, keeping the Eloquent API consistent across your codebase.

How a Relation Is Actually Built

Every relation extends Illuminate\Database\Eloquent\Relations\Relation. The two methods you must implement are:

• addConstraints() — called immediately when the relation is instantiated on a single model, adds the WHERE clause for eager loading.
• addEagerConstraints(array $models) — called during eager loading, replaces the single-model constraint with an IN (...) clause across all parent keys.

The third required method is initRelation(array $models, string $relation) and match(), which hydrate the loaded records back onto the parent models.

namespace App\Relations;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Relation;

class LatestOfMany extends Relation
{
    public function __construct(
        Builder $query,
        Model $parent,
        protected string $foreignKey,
        protected string $localKey,
        protected string $orderColumn,
    ) {
        parent::__construct($query, $parent);
    }

    public function addConstraints(): void
    {
        if (static::$constraints) {
            $this->query
                ->where($this->foreignKey, $this->parent->{$this->localKey})
                ->orderByDesc($this->orderColumn)
                ->limit(1);
        }
    }

    public function addEagerConstraints(array $models): void
    {
        $keys = collect($models)->pluck($this->localKey)->unique()->values();

        // Use a subquery per parent key to avoid the N+1 trap
        $this->query->whereIn($this->foreignKey, $keys);
    }

    public function initRelation(array $models, $relation): array
    {
        foreach ($models as $model) {
            $model->setRelation($relation, null);
        }
        return $models;
    }

    public function match(array $models, Collection $results, $relation): array
    {
        $dictionary = $results->keyBy($this->foreignKey);

        foreach ($models as $model) {
            $key = $model->{$this->localKey};
            $model->setRelation($relation, $dictionary->get($key));
        }
        return $models;
    }

    public function getResults(): mixed
    {
        return $this->query->first();
    }
}

Register it on the parent model:

class Order extends Model
{
    public function latestShipment(): LatestOfMany
    {
        return new LatestOfMany(
            Shipment::query(),
            $this,
            foreignKey: 'order_id',
            localKey: 'id',
            orderColumn: 'dispatched_at',
        );
    }
}

Now Order::with('latestShipment')->get() works exactly like any built-in relation — no raw SQL leaking into controllers.

Tapping the Query Builder Directly

When you need a covering aggregate without a relation, reach for withAggregate or drop to the grammar layer:

// Built-in — generates a correlated subquery
$orders = Order::withSum('items', 'quantity')->get();

// Manual subquery column — same idea, full control
$orders = Order::addSelect([
    'latest_dispatch' => Shipment::select('dispatched_at')
        ->whereColumn('order_id', 'orders.id')
        ->orderByDesc('dispatched_at')
        ->limit(1),
])->get();

For window functions, bypass Eloquent entirely and use DB::table() with a raw expression, then hydrate manually:

$rows = DB::table('shipments')
    ->selectRaw(
        'order_id,
         dispatched_at,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY dispatched_at DESC) AS rn'
    )
    ->toBase() // returns the underlying QueryBuilder, no Eloquent overhead
    ->get()
    ->where('rn', 1);

Testing the Custom Relation

With Pest, assert eager loading produces no extra queries:

it('eager-loads latest shipment without N+1', function () {
    $orders = Order::factory(5)->create();
    Shipment::factory()->for($orders->first())->create();

    $queryCount = 0;
    DB::listen(fn () => $queryCount++);

    Order::with('latestShipment')->get();

    expect($queryCount)->toBe(2); // one for orders, one for shipments
});

Key Takeaways

• addConstraints and addEagerConstraints are the two hooks that separate single-model access from eager loading — get them wrong and you reintroduce N+1.
• match() is pure PHP array work; keep it O(n) by keying the results collection before the loop.
• withAggregate / addSelect subqueries are often faster than a join when the aggregate touches only a few rows per parent.
• Always verify query count in tests — a custom relation that silently falls back to N+1 is worse than no relation at all.