PHPackages                             rgalstyan/laravel-aggregated-queries - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. rgalstyan/laravel-aggregated-queries

ActiveLibrary[Database & ORM](/categories/database)

rgalstyan/laravel-aggregated-queries
====================================

Optimize Laravel queries using SQL JSON aggregation (belongsTo/hasOne/hasMany in a single query)

v1.0.1(4mo ago)1011MITPHPPHP ^8.2CI passing

Since Dec 12Pushed 4mo ago1 watchersCompare

[ Source](https://github.com/rgalstyan/laravel-aggregated-queries)[ Packagist](https://packagist.org/packages/rgalstyan/laravel-aggregated-queries)[ Docs](https://github.com/rgalstyan/laravel-aggregated-queries)[ RSS](/packages/rgalstyan-laravel-aggregated-queries/feed)WikiDiscussions main Synced 1mo ago

READMEChangelog (2)Dependencies (6)Versions (3)Used By (0)

Laravel Aggregated Queries
==========================

[](#laravel-aggregated-queries)

[![Latest Version](https://camo.githubusercontent.com/445625e11b6f17d050d85c9811a8919c580ef5bbce154e448fcd33bab289e7df/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7267616c737479616e2f6c61726176656c2d616767726567617465642d717565726965732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/rgalstyan/laravel-aggregated-queries)[![Total Downloads](https://camo.githubusercontent.com/7cffea37416a24640a0f09028a8866ab9c7dc28fd263056a32abd000b50d5de9/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f7267616c737479616e2f6c61726176656c2d616767726567617465642d717565726965732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/rgalstyan/laravel-aggregated-queries)[![Tests](https://camo.githubusercontent.com/7ba67df5b6ed996f7b94d791037906f363cfbff9fb7f505bb54792b3ce32c102/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7267616c737479616e2f6c61726176656c2d616767726567617465642d717565726965732f74657374732e796d6c3f6272616e63683d6d61696e266c6162656c3d7465737473267374796c653d666c61742d737175617265)](https://github.com/rgalstyan/laravel-aggregated-queries/actions)[![License](https://camo.githubusercontent.com/172dac85f4bcbe252ae5481aac8300c3ff3bccda03302b9575012c5d3fbd0d64/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f6c2f7267616c737479616e2f6c61726176656c2d616767726567617465642d717565726965732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/rgalstyan/laravel-aggregated-queries)

Reduce multi-relation Eloquent queries to a single optimized SQL statement using JSON aggregation.

Perfect for read-heavy APIs, dashboards, and admin panels where traditional eager loading generates too many queries.

---

The Problem
-----------

[](#the-problem)

Even with proper eager loading, Laravel generates one query per relation:

```
Partner::with(['profile', 'country', 'promocodes'])->get();
```

**Produces 4 separate queries:**

```
SELECT * FROM partners
SELECT * FROM partner_profiles WHERE partner_id IN (...)
SELECT * FROM countries WHERE id IN (...)
SELECT * FROM partner_promocodes WHERE partner_id IN (...)
```

Complex pages easily generate **5–15 queries**, increasing:

- Database round-trips
- Response time
- Memory usage
- Server load

---

The Solution
------------

[](#the-solution)

Transform multiple queries into **one optimized SQL statement** using JSON aggregation:

```
Partner::aggregatedQuery()
    ->withJsonRelation('profile')
    ->withJsonRelation('country')
    ->withJsonCollection('promocodes')
    ->get();
```

**Generates a single 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 partner_promocodes WHERE partner_id = base.id) AS promocodes
FROM partners AS base
LEFT JOIN partner_profiles profile ON profile.partner_id = base.id
LEFT JOIN countries country ON country.id = base.country_id
```

**Result:**

- ✅ 1 database round-trip instead of 4
- ✅ Up to 6x faster response time
- ✅ 90%+ less memory usage
- ✅ Consistent array output

---

Performance
-----------

[](#performance)

Real benchmark on **2,000 partners** with 4 relations (50 records fetched):

MethodTimeMemoryQueriesTraditional Eloquent27.44ms2.06MB5Aggregated Query4.41ms0.18MB1**Improvement****⚡ 83.9% faster****💾 91.3% less****🔢 80% fewer**At scale (10,000 API requests/day):

- **40,000 fewer database queries**
- **3.8 minutes saved in response time**
- **18.6GB less memory usage**

---

Requirements
------------

[](#requirements)

ComponentVersionPHP^8.2Laravel^10.0 | ^11.0 | ^12.0MySQL^8.0PostgreSQL^12.0---

Installation
------------

[](#installation)

```
composer require rgalstyan/laravel-aggregated-queries
```

---

Quick Start
-----------

[](#quick-start)

### 1. Add trait to your model

[](#1-add-trait-to-your-model)

```
use Rgalstyan\LaravelAggregatedQueries\HasAggregatedQueries;

class Partner extends Model
{
    use HasAggregatedQueries;

    public function profile() { return $this->hasOne(PartnerProfile::class); }
    public function country() { return $this->belongsTo(Country::class); }
    public function promocodes() { return $this->hasMany(PartnerPromocode::class); }
}
```

### 2. Query with aggregation

[](#2-query-with-aggregation)

```
$partners = Partner::aggregatedQuery()
    ->withJsonRelation('profile', ['id', 'name', 'email'])
    ->withJsonRelation('country', ['id', 'name', 'code'])
    ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
    ->withCount('promocodes')
    ->where('is_active', true)
    ->orderBy('created_at', 'desc')
    ->limit(50)
    ->get();
```

### 3. Use the data

[](#3-use-the-data)

```
foreach ($partners as $partner) {
    echo $partner['name'];
    echo $partner['profile']['email'] ?? 'N/A';
    echo $partner['country']['name'];
    echo "Promocodes: " . count($partner['promocodes']);
    echo "Count: " . $partner['promocodes_count'];
}
```

**Output structure (guaranteed):**

```
[
    'id' => 1,
    'name' => 'Partner A',
    'is_active' => true,
    'profile' => ['id' => 10, 'name' => 'John', 'email' => 'john@example.com'], // array or null
    'country' => ['id' => 1, 'name' => 'USA', 'code' => 'US'],                   // array or null
    'promocodes' => [                                                             // always array, never null
        ['id' => 1, 'code' => 'SAVE10', 'discount' => 10],
        ['id' => 2, 'code' => 'SAVE20', 'discount' => 20],
    ],
    'promocodes_count' => 2
]
```

---

Advanced Usage
--------------

[](#advanced-usage)

### Reuse existing queries

[](#reuse-existing-queries)

Already have complex query logic? Pass it as base:

```
$baseQuery = Partner::query()
    ->whereHas('profile', fn($q) => $q->where('verified', true))
    ->where('country_id', '!=', null)
    ->latest();

$partners = Partner::aggregatedQuery($baseQuery)
    ->withJsonRelation('profile')
    ->withJsonRelation('country')
    ->get();
```

The base query becomes a subquery, preserving all your filters, scopes, and joins.

### Automatic column detection

[](#automatic-column-detection)

When using `['*']`, the package automatically detects columns from model's `$fillable`:

```
Partner::aggregatedQuery()
    ->withJsonRelation('profile') // Auto-detects: ['id', 'partner_id', 'name', 'email', 'created_at', 'updated_at']
    ->get();
```

No database metadata queries needed! Works with:

- Custom primary keys (`uuid` instead of `id`)
- Custom timestamp columns
- Soft deletes (`deleted_at`)

### Explicit columns (recommended)

[](#explicit-columns-recommended)

For best performance, specify columns explicitly:

```
Partner::aggregatedQuery()
    ->withJsonRelation('profile', ['id', 'name', 'email'])     // ✅ Fast
    ->withJsonRelation('country', ['id', 'name'])              // ✅ Fast
    ->withJsonRelation('profile')                              // ⚠️ Slower (auto-detects columns)
    ->get();
```

---

API Reference
-------------

[](#api-reference)

### Loading Relations

[](#loading-relations)

```
// Load single relation (belongsTo, hasOne)
->withJsonRelation(string $relation, array $columns = ['*'])

// Load collection (hasMany)
->withJsonCollection(string $relation, array $columns = ['*'])

// Count related records
->withCount(string $relation)
```

### Query Filters

[](#query-filters)

```
->where(string $column, mixed $value)
->where(string $column, string $operator, mixed $value)
->whereIn(string $column, array $values)
->orderBy(string $column, string $direction = 'asc')
->limit(int $limit)
->offset(int $offset)
```

### Execution

[](#execution)

```
->get()                    // Collection of arrays (default, fastest)
->get('array')             // Same as above
->get('eloquent')          // Hydrate into Eloquent models (not recommended)
->first()                  // Get first result
->paginate(int $perPage)   // Laravel paginator
```

### Debugging

[](#debugging)

```
->toSql()                  // Get generated SQL
->getBindings()            // Get query bindings
->debug()                  // Log SQL + execution time
```

---

When to Use
-----------

[](#when-to-use)

### ✅ Perfect for:

[](#-perfect-for)

- **API endpoints** with multiple relations
- **Admin dashboards** with complex data
- **Mobile backends** where latency matters
- **Listings/tables** with 3–10 relations
- **Read-heavy services** (90%+ reads)
- **High-traffic applications** needing DB optimization

### ⚠️ Not suitable for:

[](#️-not-suitable-for)

- **Write operations** (use standard Eloquent)
- **Model events/observers** (results are arrays by default)
- **Deep nested relations** like `profile.company.country` (not yet supported)
- **Polymorphic relations** (`morphTo`, `morphMany`)
- **Many-to-many** (`belongsToMany`)

---

Important Constraints
---------------------

[](#important-constraints)

### Read-Only by Design

[](#read-only-by-design)

Results are **arrays**, not Eloquent models (by default).

This means:

- ❌ No model events (`created`, `updated`, `deleted`)
- ❌ No observers
- ❌ No mutators/accessors
- ❌ Cannot call `save()`, `update()`, `delete()`

**Use for read operations only.** For writes, use standard Eloquent.

### Data Shape Guarantees

[](#data-shape-guarantees)

FeatureAlways Returns`withJsonRelation()``array` or `null``withJsonCollection()``array` (empty `[]` if no records)`withCount()``integer`No surprises. No `null` collections. Consistent types.

---

Batch Processing
----------------

[](#batch-processing)

For large exports, use chunks:

```
Partner::query()->chunkById(500, function ($partners) {
    $ids = $partners->pluck('id');

    $data = Partner::aggregatedQuery()
        ->withJsonRelation('country')
        ->withJsonCollection('promocodes')
        ->whereIn('id', $ids)
        ->get();

    // Export to CSV, send to queue, etc.
});
```

**Do NOT** use `limit(5000)` — chunk it instead!

---

Configuration
-------------

[](#configuration)

Publish config file:

```
php artisan vendor:publish --tag=aggregated-queries-config
```

**config/aggregated-queries.php:**

```
return [
    // Maximum allowed limit (safety)
    'max_limit' => 500,

    // Column cache for models without $fillable
    'column_cache' => [
        'some_table' => ['id', 'name', 'created_at'],
    ],
];
```

---

Limitations (v1.x)
------------------

[](#limitations-v1x)

Currently **not supported** (planned for future versions):

- Nested relations (`profile.company.country`)
- Callbacks in relations (`withCount('posts', fn($q) => $q->published())`)
- `belongsToMany` (many-to-many)
- `morphTo` / `morphOne` / `morphMany`
- Query scopes via `__call`
- Automatic result caching

---

Examples
--------

[](#examples)

See `/examples` directory:

- [`basic-usage.php`](examples/basic-usage.php) - Simple queries
- [`multiple-relations.php`](examples/multiple-relations.php) - Complex relations
- [`with-filters.php`](examples/with-filters.php) - Filtering and sorting
- [`pagination.php`](examples/pagination.php) - Paginated results
- [`batch-export.php`](examples/batch-export.php) - Chunk processing

---

Testing
-------

[](#testing)

```
composer install

# Run tests
composer test

# Run tests with coverage
composer test:coverage

# Static analysis
composer phpstan

# Code formatting
composer format
```

---

Contributing
------------

[](#contributing)

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Add tests for new features
4. Run `composer test` and `composer phpstan`
5. Submit a pull request

---

Security
--------

[](#security)

If you discover a security vulnerability, please email:

📧 ****

Do not create public issues for security vulnerabilities.

---

Changelog
---------

[](#changelog)

See [CHANGELOG.md](CHANGELOG.md) for release history.

---

License
-------

[](#license)

The MIT License (MIT). See [LICENSE](LICENSE) for details.

---

Credits
-------

[](#credits)

**Author:** Razmik Galstyan
**GitHub:** [@rgalstyan](https://github.com/rgalstyan)
**Email:**

Built with ❤️ for the Laravel community.

---

Support
-------

[](#support)

- ⭐ Star the repo if you find it useful
- 🐛 Report bugs via [GitHub Issues](https://github.com/rgalstyan/laravel-aggregated-queries/issues)
- 💡 Feature requests welcome
- 📖 Improve docs via pull requests

###  Health Score

37

—

LowBetter than 83% of packages

Maintenance74

Regular maintenance activity

Popularity11

Limited adoption so far

Community7

Small or concentrated contributor base

Maturity48

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~4 days

Total

2

Last Release

147d ago

PHP version history (2 changes)v1.0.0PHP ^8.1

v1.0.1PHP ^8.2

### Community

Maintainers

![](https://www.gravatar.com/avatar/ea0e30900e99add4f467a7803e89aa70a451432bced62160f16d81980182a5e9?d=identicon)[rgalstyan](/maintainers/rgalstyan)

---

Top Contributors

[![rgalstyan](https://avatars.githubusercontent.com/u/5991398?v=4)](https://github.com/rgalstyan "rgalstyan (15 commits)")

---

Tags

aggregate-functionsbenchmarkingdatabaseeloquentjson-aggregationlaravellaravel-packagememory-usagemysqloptimizationperformanceperformance-tuningpostgresqlquery-buildersql-optimizationjsonlaraveldatabaseperformancesqleloquentoptimizationeager-loadingquery-optimizationaggregate

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Code StyleLaravel Pint

Type Coverage Yes

### Embed Badge

![Health badge](/badges/rgalstyan-laravel-aggregated-queries/health.svg)

```
[![Health](https://phpackages.com/badges/rgalstyan-laravel-aggregated-queries/health.svg)](https://phpackages.com/packages/rgalstyan-laravel-aggregated-queries)
```

###  Alternatives

[mongodb/laravel-mongodb

A MongoDB based Eloquent model and Query builder for Laravel

7.1k7.2M71](/packages/mongodb-laravel-mongodb)[spiritix/lada-cache

A Redis based, automated and scalable database caching layer for Laravel

591444.8k2](/packages/spiritix-lada-cache)[pdphilip/elasticsearch

An Elasticsearch implementation of Laravel's Eloquent ORM

145360.2k4](/packages/pdphilip-elasticsearch)[sarfraznawaz2005/indexer

Laravel package to monitor SELECT queries and offer best possible INDEX fields.

562.7k](/packages/sarfraznawaz2005-indexer)[msafadi/laravel-eloquent-join-with

Laravel Eloquent Join With Relationships

1646.0k](/packages/msafadi-laravel-eloquent-join-with)[eusonlito/laravel-database-cache

Cache Database Query results on Laravel Query Builder or Eloquent

194.2k](/packages/eusonlito-laravel-database-cache)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)