DEV Community

Raz Galstyan
Raz Galstyan

Posted on

I Made My Laravel API 83% Faster by Rethinking Database Queries

#laravel #database #performance #php

How I solved the N+1 query problem using JSON aggregation instead of traditional eager loading
tags: laravel, php, performance, database.

Last month, I was debugging a slow admin dashboard. The page loaded 500 partner records with their profiles, countries, and promotional codes. Each page load took over 2 seconds.

The culprit? The classic N+1 query problem.

The Problem Everyone Knows

Even with Laravel's eager loading, I was still hitting the database 5 times per request:

$partners = Partner::with(['profile', 'country', 'promocodes'])->get();
Enter fullscreen mode Exit fullscreen mode

This generates:

SELECT * FROM partners                        -- Query 1
SELECT * FROM profiles WHERE partner_id IN... -- Query 2  
SELECT * FROM countries WHERE id IN...        -- Query 3
SELECT * FROM promocodes WHERE partner_id IN...-- Query 4
Enter fullscreen mode Exit fullscreen mode

Each query means another round-trip to the database. With 50 records, that's 4 network round-trips, adding 15-20ms of latency each.

The "Aha!" Moment

I asked myself: "Can we load everything in ONE query?"

That's when I remembered MySQL's JSON_OBJECT and JSON_ARRAYAGG functions. What if instead of multiple queries, we could aggregate all relations into JSON directly in SQL?

The Solution: JSON Aggregation

I built a Laravel package that does exactly this:

$partners = Partner::aggregatedQuery()
    ->withJsonRelation('profile')
    ->withJsonRelation('country')
    ->withJsonCollection('promocodes')
    ->get();
Enter fullscreen mode Exit fullscreen mode

This generates a single optimized query:

SELECT 
    base.*,
    JSON_OBJECT('id', profile.id, 'name', profile.name) AS profile,
    JSON_OBJECT('id', country.id, 'name', country.name) AS country,
    (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'code', code))
     FROM promocodes WHERE partner_id = base.id) AS promocodes
FROM partners base
LEFT JOIN profiles profile ON profile.partner_id = base.id
LEFT JOIN countries country ON country.id = base.country_id
Enter fullscreen mode Exit fullscreen mode

One query. All the data.

The Results

I ran benchmarks on a dataset of 2,000 partners with 4 relations each, fetching 50 records:

Method Time Memory Queries
Standard Eloquent 27.44ms 2.06MB 4
JSON Aggregation 4.41ms 0.18MB 1
Improvement 83% faster 91% less 75% fewer

That's not a typo. 83% faster.

Why Is It So Fast?

The performance gain comes from three factors:

1. Network Latency (80% of the gain)

Database round-trips are expensive. Even on localhost, each query adds 5-10ms. On a remote database? 15-20ms each.

Before: 4 queries × 15ms = 60ms in network time

After: 1 query × 15ms = 15ms

2. Avoiding Eloquent Hydration (15% of the gain)

By returning arrays instead of Eloquent models, we skip:

  • Object instantiation
  • Attribute casting
  • Relationship binding
  • Event firing

3. Optimized SQL (5% of the gain)

The database does the aggregation work using highly optimized C code instead of PHP loops.

Real-World Impact

On my dashboard handling 10,000 API requests per day:

  • 40,000 fewer database queries
  • 4 minutes saved in total response time
  • 19GB less memory usage
  • Better server resource utilization

How It Works

Installation 

composer require rgalstyan/laravel-aggregated-queries
Enter fullscreen mode Exit fullscreen mode

Setup

Add the trait to your model:

use Rgalstyan\LaravelAggregatedQueries\HasAggregatedQueries;

class Partner extends Model
{
    use HasAggregatedQueries;

    public function profile() {
        return $this->hasOne(PartnerProfile::class);
    }

    public function promocodes() {
        return $this->hasMany(PartnerPromocode::class);
    }
}
Enter fullscreen mode Exit fullscreen mode

Usage 

// Instead of this:
$partners = Partner::with(['profile', 'promocodes'])->get();

// Do this:
$partners = Partner::aggregatedQuery()
    ->withJsonRelation('profile', ['id', 'name', 'email'])
    ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
    ->where('is_active', true)
    ->get();
Enter fullscreen mode Exit fullscreen mode

Output

The data structure is predictable and clean:

[
    'id' => 1,
    'name' => 'Partner A',
    'profile' => [
        'id' => 10,
        'name' => 'John',
        'email' => 'john@example.com'
    ],
    'promocodes' => [
        ['id' => 1, 'code' => 'SAVE10'],
        ['id' => 2, 'code' => 'SAVE20']
    ]
]
Enter fullscreen mode Exit fullscreen mode

Guaranteed:

  • Relations are array or null (never undefined)
  • Collections are always array (never null)
  • No surprises in your code

When Should You Use This?

✅ Perfect for:

  • API endpoints with multiple relations
  • Admin dashboards with complex queries
  • Mobile backends where every millisecond counts
  • Read-heavy applications (90%+ reads)
  • High-traffic services needing optimization

⚠️ Not recommended for:

  • Write operations (use standard Eloquent)
  • When you need model events/observers
  • Complex nested relations (coming in v1.1!)

Performance vs. Eloquent Models

The package offers two modes:

// Array mode (default, fastest - 83% faster)
$partners = Partner::aggregatedQuery()->get();

// Eloquent mode (still faster - 27% improvement)
$partners = Partner::aggregatedQuery()->get('eloquent');
Enter fullscreen mode Exit fullscreen mode

Array mode is fastest because it skips Eloquent's hydration overhead. But even in Eloquent mode, you still save 1 database query, which gives a significant boost.

Trade-offs

Let's be honest about limitations:

What you lose:

  • Model events (no created, updated, deleted events)
  • Accessors/mutators (in array mode)
  • The ability to call save() or update() (read-only)

What you gain:

  • 83% faster response times
  • 91% less memory usage
  • Simpler, more predictable data structures
  • Better scalability

For read-heavy operations like APIs and dashboards, this trade-off is absolutely worth it.

What's Next?

I'm currently working on v1.1.0 with:

  • Nested relations (profile.company.country)
  • Conditional loading with query constraints
  • Relation aliasing
  • Enhanced debugging tools

Try It Out!

If you're building APIs or dashboards with Laravel, give it a try:

🔗 GitHub: rgalstyan/laravel-aggregated-queries

📦 Packagist: composer require rgalstyan/laravel-aggregated-queries

I'd love to hear your results! Have you tried optimizing N+1 queries in other ways? Drop a comment below.

P.S. The package has been featured in Laravel News! If you find it useful, a GitHub star would mean a lot ⭐

Top comments (6)

Collapse
 
xwero profile image
david duymelinck •

I think the best solution for your case is working with the query builder or just use a raw SQL query.
Wanting to fit the query in an Eloquent jacket seems like looking for problems you do not need to solve.

I've been on a journey this year where I also started with doing everything in an ORM, and now I'm creating a library that only abstracts the database field and table names.
The main thing I figured out is that I don't want to burden myself having both the SQL and ORM mental model in my head when working with databases. When it comes down to it only the SQL mental model is the important one.

Collapse
 
rgalstyan profile image
Raz Galstyan •

That’s a totally fair point, and I actually agree with a large part of it 👍

At a certain scale, everything is SQL, and understanding the SQL mental model is non-negotiable.
This package isn’t trying to hide SQL or pretend it doesn’t exist.

The problem I’m trying to solve is a slightly different one:

  • Many teams already use Eloquent as their data access layer
  • For read-heavy endpoints (lists, dashboards, APIs), Eloquent relationships often turn into N+1 queries or heavy eager loading
  • Switching every such query to raw SQL or Query Builder works, but it usually means:
    • losing relation definitions
    • duplicating column mappings
    • mixing raw SQL and ORM logic inconsistently across the codebase

This package is essentially a thin, explicit escape hatch:

  • you still think in SQL (joins, grouping, aggregation)
  • but you reuse existing Eloquent metadata (tables, keys, relations)
  • and you get predictable, single-query results for DTO-style reads

It’s not meant to replace raw SQL or the Query Builder.
If a team is already comfortable writing raw SQL everywhere, that’s often the best solution.

This is more for teams that:

  • want to keep Eloquent as the primary model layer
  • but need a clean, explicit way to say: “this endpoint is read-only, optimized, and SQL-driven”

So in short: I don’t see it as putting SQL into an Eloquent jacket,
but rather using Eloquent as a schema/metadata layer, while staying fully aware of the SQL underneath.

Collapse
 
xwero profile image
david duymelinck •

duplicating column mappings

Eloquent has no column mappings. It is only when you start using accessors an mutators that Eloquent starts to abstract columns.

And if you really need a mapper, I recommend standalone mapper solutions like the Symfony object mapper.

mixing raw SQL and ORM logic inconsistently across the codebase

ORM's are notoriously lacking in SQL features because one of their jobs is to work for all supported databases.
This means using raw SQL is a part of using an ORM when you are doing more than just CRUD queries.
So I don't really see how the mixing is the negative thing to do?

want to keep Eloquent as the primary model layer

For me it is this thinking that gives many Lavavel applications technical debt.
Eloquent looks easy in the code but Users::all() is just SELECT * FROM users, is one really harder than the other?
Is using SQL joins harder than the different types of relationships?
A lot of people, me included, start to use an ORM because it is part of the framework. The question should be, which problem is the ORM solving for my application?

while staying fully aware of the SQL underneath.

The more you think in abstractions, the less you are connected to the inner workings of the code.
If you ask 100 Laravel developers how much queries eager loading is generating, how many would know the right answer? It is advertised as the least amount of queries, and that is not even true.
The more complex querying you add, the more people are going to tune out the underlying SQL.

That is the reason I am not a big fan of libraries like yours. It is not that I don't appreciate the effort. And I do think you do it to help others.

Thread Thread
 
rgalstyan profile image
Raz Galstyan •

Thanks for taking the time to write this — I get where you’re coming from, and I agree with a lot of it.

A few clarifications from my side:

1) “Duplicating column mappings”
You’re right that Eloquent isn’t a strict mapper like Doctrine. I used “mapping” loosely to mean “the implicit contract between model attributes and DB columns” (casts, hidden/visible, custom keys, timestamps, soft deletes, naming conventions, accessors/mutators, etc.). Once a codebase relies on that contract, people often end up re-implementing the same assumptions when they move to raw SQL/Query Builder everywhere.

2) “Mixing raw SQL and ORM logic”
I agree that real apps inevitably mix them. The problem I’ve seen isn’t the mixing itself — it’s the inconsistent mixing:

  • different developers solving the same “listing + relations” problem in different ad-hoc ways
  • varying output shapes (null vs [], duplicate rows, hydration surprises)
  • performance fixes scattered in controllers/services with no conventions

My goal is to provide a very explicit, narrowly scoped pattern: read-only, DTO-style lists where the output shape is stable and the query count is predictable.

3) “Keeping Eloquent as the primary model layer causes debt”
Totally valid in many teams. I’m not trying to push “ORM everywhere”. I’m saying: if a team has already committed to Eloquent for the domain layer, there’s still a big gap for read-heavy endpoints where:

  • eager loading becomes many queries
  • or you end up writing raw SQL anyway

This library is basically a “named escape hatch” for that gap.

4) “Abstractions disconnect people from SQL”
Also true. That’s why the package tries to be the opposite of “magic”:

  • it’s opt-in
  • it’s read-only by design
  • it encourages looking at the generated SQL and using indexes
  • it makes the trade-offs obvious (you’re choosing a DTO/read-model approach)

So I fully respect the “just write SQL” philosophy — for many teams that’s the best path.
This library is for teams that want an explicit, consistent read-model approach while still living in an Eloquent-based codebase.

Appreciate the thoughtful pushback.

Thread Thread
 
xwero profile image
david duymelinck •

The first two points about the inconsistent mixing, why should those be for a library to solve? Isn't that a job for the application?

Eloquent for the domain layer

Don't jump scare me with statements like that. Eloquent has no place in a domain layer.

just write SQL

I do lean to that side, but I'm not against an ORM. An ORM is just a big beast to throw in an application from the start.

Thread Thread
 
rgalstyan profile image
Raz Galstyan •

Yes, I agree with you that consistency and architecture are ultimately the application/team’s responsibility.

My point isn’t that a library can “fix” a poorly designed application.
Any tool can be misused — raw SQL, Query Builder, Eloquent, or my package.

What I’m aiming for is simply a reusable, explicit pattern for a very specific area:
read-only, DTO-style listing endpoints where you want predictable query count
and a stable output shape.

In a well-structured app this would live in the infrastructure/read-model/query layer,
not in the domain layer. I should have used that wording instead of “domain layer”.

So we’re probably closer than it sounds:
good architecture is required either way — the package just provides a consistent
implementation option for one common performance-sensitive use case.