PHPackages                             rgalstyan/symfony-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/symfony-aggregated-queries

ActiveSymfony-bundle[Database & ORM](/categories/database)

rgalstyan/symfony-aggregated-queries
====================================

Reduce multi-relation Doctrine queries to a single SQL statement using JSON aggregation. Solves Doctrine's N+1 problem.

v1.0.2(4mo ago)41MITPHPPHP ^8.1CI passing

Since Dec 16Pushed 4mo agoCompare

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

READMEChangelog (3)Dependencies (13)Versions (4)Used By (0)

Symfony Aggregated Queries
==========================

[](#symfony-aggregated-queries)

[![Latest Version](https://camo.githubusercontent.com/412ec393bef692d66fdb38171a56de08ecf68d295ff4289780c5151baabdbfbc/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7267616c737479616e2f73796d666f6e792d616767726567617465642d717565726965732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/rgalstyan/symfony-aggregated-queries)[![Total Downloads](https://camo.githubusercontent.com/c4881cb09097561d05eea1c0237632d6b6285a0ea97ddb4a8624c017e7fb96c1/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f7267616c737479616e2f73796d666f6e792d616767726567617465642d717565726965732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/rgalstyan/symfony-aggregated-queries)[![Tests](https://camo.githubusercontent.com/5107a32b9ab5ae3aa215018c5491b68f15e12dfc3c3a332e8cde9628539adf0e/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7267616c737479616e2f73796d666f6e792d616767726567617465642d717565726965732f73796d666f6e792e796d6c3f6272616e63683d6d61696e266c6162656c3d7465737473267374796c653d666c61742d737175617265)](https://github.com/rgalstyan/symfony-aggregated-queries/actions)[![License](https://camo.githubusercontent.com/e78a46664c396ee9e690902a3274adf37cdc1f686eb92726733bd9c1fa805324/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f6c2f7267616c737479616e2f73796d666f6e792d616767726567617465642d717565726965732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/rgalstyan/symfony-aggregated-queries)

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

**Solves Doctrine's documented N+1 problem** ([Issue #4762](https://github.com/doctrine/orm/issues/4762)) where `fetch="EAGER"` still generates multiple queries for OneToMany/ManyToMany relations.

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

---

🔥 The Problem
-------------

[](#-the-problem)

When querying Doctrine entities, relations are loaded either lazily (classic N+1) or eagerly using **separate queries for collections**.

Example with traditional Doctrine entity loading:

```
$qb = $entityManager->createQueryBuilder();
$partners = $qb
    ->select('p, profile, country')
    ->from(Partner::class, 'p')
    ->leftJoin('p.profile', 'profile')
    ->leftJoin('p.country', 'country')
    ->getQuery()
    ->getResult();
```

**Still produces 3–4 separate queries:**

```
SELECT ... FROM partners p
    LEFT JOIN profiles profile ON ...
    LEFT JOIN countries country ON ...

    SELECT ... FROM partner_promocodes WHERE partner_id IN (...) -- additional eager-loading query
SELECT ... FROM discount_rules WHERE promocode_id IN (...)
```

While this is not a fetch join, it still results in multiple database round-trips, heavy Doctrine hydration, increased memory usage, and slower response times.

Fetch joins can reduce the number of queries to one, but introduce row explosion, break pagination, and do not scale when multiple collections are involved.

**Doctrine's Known Issue:**
Even with `fetch="EAGER"`, OneToMany and ManyToMany relations cause N+1 queries. This is a [documented limitation](https://github.com/doctrine/orm/issues/4762) that has existed since 2015.

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

- Database round-trips
- Doctrine hydration overhead
- Response time
- Memory usage
- Server load

---

✨ The Solution
--------------

[](#-the-solution)

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

```
$partners = $partnerRepository->aggregatedQuery()
    ->withJsonRelation('profile', ['id', 'name', 'email'])
    ->withJsonRelation('country', ['id', 'name', 'code'])
    ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
    ->withCount('promocodes')
    ->getResult();
```

**Generates a single query:**

```
SELECT e.*,
    JSON_OBJECT('id', rel_profile.id, 'name', rel_profile.name, 'email', rel_profile.email) AS profile,
    JSON_OBJECT('id', rel_country.id, 'name', rel_country.name, 'code', rel_country.code) AS country,
    (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'code', code, 'discount', discount))
     FROM partner_promocodes WHERE partner_id = e.id) AS promocodes,
    (SELECT COUNT(*) FROM partner_promocodes WHERE partner_id = e.id) AS promocodes_count
FROM partners e
LEFT JOIN partner_profiles rel_profile ON rel_profile.partner_id = e.id
LEFT JOIN countries rel_country ON rel_country.id = e.country_id
```

**Result:**

- ✅ 1 database round-trip instead of 4
- ✅ No Doctrine hydration overhead (uses DBAL directly)
- ✅ Up to 7x faster response time
- ✅ 90%+ less memory usage
- ✅ Consistent array output

---

📊 Performance
-------------

[](#-performance)

Real-world benchmark using a Symfony application with a realistic e-commerce dataset (products, images, reviews).

**Dataset context:**

- ~100,000 products
- ~300,000 images
- ~500,000 reviews

Example run (**limit = 1000**):

MethodTimeMemoryQueriesTraditional Eloquent / Doctrine-style loading167.49ms24.37 MB23Aggregated Query28.17ms6.91 MB1**Improvement****⚡ 83.2% faster****💾 71.6% less****🔢 22 fewer**> These numbers depend on hardware and DB state, but the overall trend is consistent. The primary gains come from reduced SQL round-trips and avoiding ORM entity hydration.

### Reproducible benchmark project

[](#reproducible-benchmark-project)

A standalone Symfony-based benchmark with full setup instructions and fixtures is available here:

- ****

The benchmark focuses on **read-only, DTO-style queries** and compares traditional ORM loading versus aggregated SQL queries in real-world conditions.

📋 Requirements
--------------

[](#-requirements)

ComponentVersionPHP^8.1Symfony^6.0 | ^7.0Doctrine ORM^2.14 | ^3.0MySQL^8.0PostgreSQL^12.0---

📦 Installation
--------------

[](#-installation)

### 1. Install via Composer

[](#1-install-via-composer)

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

### 2. Enable the Bundle

[](#2-enable-the-bundle)

If you're using **Symfony Flex**, the bundle is automatically registered.

Otherwise, add to `config/bundles.php`:

```
return [
    // ...
    Rgalstyan\SymfonyAggregatedQueries\Bundle\SymfonyAggregatedQueriesBundle::class => ['all' => true],
];
```

### 3. (Optional) Configure

[](#3-optional-configure)

Create `config/packages/aggregated_queries.yaml`:

```
aggregated_queries:
    enabled: true
    debug: '%kernel.debug%'
    max_relations: 15
    default_hydrator: 'array'  # array|entity
```

---

🚀 Quick Start
-------------

[](#-quick-start)

### 1. Add trait to your repository

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

```