Laravel Aggregated Queries Laravel Package

Getting Started

Install and confirm driver support first — this is the whole ballgame.

composer require rgalstyan/laravel-aggregated-queries

The package replaces the "one query for the parent, one query per relation" pattern with a single query that builds related rows into a JSON column using the database's native aggregation functions (JSON_ARRAYAGG / JSON_OBJECT on MySQL 5.7+/8, json_agg / json_build_object on PostgreSQL, json_group_array on SQLite 3.38+). Before anything else, verify your production driver and version support the functions — SQLite in CI and MySQL in prod is the classic place this silently diverges.

Where to look first:

1. The README's usage example — the package is small (10 stars, 0 dependents), so the README is the API reference. Confirm the exact trait/scope name before writing code; don't assume from the description.
2. The service provider's boot() — check for config publishing, macro registration, or driver-specific grammar overrides.
3. The src/ directory for the SQL grammar/aggregator classes — this is where you'll look when something breaks, because there is no community StackOverflow corpus for a package this size.

First use case (the one that justifies the dependency):

An index endpoint returning 50 orders, each with its line items inline.

// Before: 1 + 50 queries (or 2 queries + in-PHP grouping)
$orders = Order::with('items')->paginate(50);

// After: 1 query, items arrive as a JSON column
// (illustrative — confirm the real scope name in the README)
$orders = Order::query()
    ->withAggregated(['items'])
    ->paginate(50);

Measure it. DB::listen() or Laravel Telescope will show the query count drop; that's your proof and your regression test.

Implementation Patterns

Treat it as an opt-in read-path tool, not a global replacement for with(). The package shines on list endpoints and exports where you serialize every child anyway. It's a poor fit for pages where you need Eloquent relationship methods, lazy loading, or per-child model events.

API resources / Inertia props. The natural home. If your OrderResource maps $this->items->map(...), aggregated JSON decoding into a collection keeps the resource identical while removing the eager-load query:

class OrderResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id'     => $this->id,
            'total'  => $this->total,
            'items'  => OrderItemResource::collection($this->items),
        ];
    }
}

The point is that the resource layer doesn't change — only the query that feeds it. That makes adoption incremental and reversible per-endpoint.

Exports and reporting. For CSV/Excel jobs iterating tens of thousands of parents, use cursor() or chunkById() with aggregation to keep memory and query count flat. This is where the win is largest, because a naive with() on a chunked export still issues a relation query per chunk.

Order::query()
    ->withAggregated(['items', 'customer'])
    ->chunkById(500, fn ($orders) => $this->writeRows($orders));

Combine with pagination carefully. Aggregation typically implies GROUP BY on the parent key. Laravel's paginate() runs a count(*) on the same query — verify the count query is still correct after grouping, or supply an explicit ->paginate($perPage, ['*'], 'page', $page) with a hand-written count. This is the single most common integration bug.

Keep hot paths narrow. Apply it to the two or three endpoints that actually show up in slow-query logs. Spreading it everywhere couples your whole codebase to a low-star dependency.

Test with query-count assertions, not just response assertions:

DB::enableQueryLog();
$this->getJson('/api/orders')->assertOk();
$this->assertCount(2, DB::getQueryLog()); // count + data

That test is your canary if the package ever changes behavior on upgrade.

Gotchas and Tips

Decoded JSON is not a hydrated Eloquent model. Depending on how the package reconstructs relations, you may get arrays, stdClass, or partially-hydrated models. Accessors, casts, $appends, and model events generally will not fire on aggregated children. If a child needs an accessor, compute it in SQL or map it in the resource — don't assume $item->formatted_price works.

Empty relations: null vs []. json_agg over zero rows returns NULL in Postgres; MySQL's JSON_ARRAYAGG returns NULL too. Expect the package to normalize this, but verify — a null where your frontend expects [] is a runtime crash in JS, not a PHP error. Add a ?? [] in the resource until you've confirmed.

Ordering inside the aggregate is not guaranteed. SQL makes no promise about row order within json_agg unless you specify it. If child order matters, look for an ordering option in the package or fall back to sorting in PHP. Sorting an unordered JSON array in the resource is cheap insurance.

Driver fragmentation is the main risk. MySQL's JSON_ARRAYAGG doesn't support ORDER BY (until 8.0.14-ish behavior varies), Postgres needs json_agg vs jsonb_agg for different performance characteristics, and SQLite's JSON1 extension must be compiled in. Test on the same engine and major version as production. If your CI uses SQLite and prod uses MySQL, add a MySQL service container to CI.

Nested relations multiply fast. withAggregated(['items.product']) means nested json_agg/json_build_object. It works, but the SQL becomes unreadable and EXPLAIN output becomes hard to interpret. Cap nesting at one level and eager-load deeper relations normally.

Debugging: read the raw SQL. ->toSql() and ->getBindings() on the aggregated query before executing. Then run EXPLAIN ANALYZE on it directly. Because the generated SQL is non-obvious, you will not reason your way to the problem — you'll read it.

Identifier quoting and injection surface. Aggregation requires column and table names as raw strings. If any part of the relation spec is user-influenced (sortable columns, dynamic includes), you must whitelist it. Never pass request input into an aggregation spec.

Model events, withCount, and withSum don't compose. If you already use withCount(), check whether it coexists with aggregation or requires a separate query. Mixing them can reintroduce the extra query you were trying to remove.

Extension points to know about: a custom aggregator/grammar class (subclass it rather than editing vendor code — you'll lose changes on composer update), and model-level query scopes if the package exposes a trait. Publish any config to config/ so defaults are overridable per environment.

Vendor risk is real. 10 stars, 0 dependents, a single maintainer, and a release dated in the future relative to typical adoption cycles means: pin an exact version ("rgalstyan/laravel-aggregated-queries": "1.0.*"), wrap it behind your own repository/scope so you can swap it out, and keep the query-count tests so you'd notice if you had to hand-roll the json_agg yourself. The underlying technique is ~30 lines of selectRaw — the package is convenience, not capability. Budget for the possibility of forking it.