Download the PHP package rgalstyan/symfony-aggregated-queries without Composer

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) 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

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:

Still produces 3–4 separate queries:

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 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

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

Generates a single query:

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

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):

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

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

• https://github.com/rgalstyan/doctrine-aggregated-queries-benchmark

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

📋 Requirements

📦 Installation

1. Install via Composer

2. Enable the Bundle

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

Otherwise, add to config/bundles.php:

3. (Optional) Configure

Create config/packages/aggregated_queries.yaml:

🚀 Quick Start

1. Add trait to your repository

2. Define your entities

3. Use in your service

4. Use in controller

5. Response structure (guaranteed)

💡 Advanced Usage

Direct Service Usage

Inject AggregatedQueryBuilder directly when you need more flexibility:

Filtering and Sorting

Collections (OneToMany)

Multiple Counts

📖 API Reference

Loading Relations

Query Filters

Execution

Debugging

✅ When to Use

Perfect for:

• ✅ API endpoints with multiple relations
• ✅ Admin dashboards with complex data views
• ✅ Mobile backends where latency matters
• ✅ Listing pages with 3–10 relations per row
• ✅ Read-heavy services (90%+ reads)
• ✅ High-traffic applications needing DB optimization
• ✅ Replacing Doctrine's N+1 problem

⚠️ Not suitable for:

• ❌ Write operations (use standard Doctrine)
• ❌ Doctrine lifecycle events (results are arrays by default)
• ❌ Deep nested relations like profile.company.country (not yet supported in v1.0)
• ❌ Polymorphic relations (not in v1.0)
• ❌ ManyToMany relations (planned for v1.1)

🔒 Important Constraints

Read-Only by Design

Results are arrays, not Doctrine entities (by default).

This means:

• ❌ No Doctrine lifecycle events (postLoad, preUpdate, etc.)
• ❌ No entity listeners
• ❌ No lazy loading
• ❌ Cannot call persist(), flush(), remove()

Use for read operations only. For writes, use standard Doctrine.

Data Shape Guarantees

No surprises. No null collections. Consistent types across MySQL and PostgreSQL.

📦 Batch Processing

For large exports or background jobs:

Do NOT use limit(5000) without batching!

⚙️ Configuration Reference

⚠️ Limitations (v1.0)

Currently not supported (planned for future versions):

• Nested relations (profile.company.country)
• ManyToMany (belongsToMany)
• Polymorphic relations (not common in Doctrine)
• Doctrine Query Language (DQL) integration
• Callbacks in relations
• Automatic result caching

See Roadmap for planned features.

🆚 Comparison: Traditional vs Aggregated

Traditional Doctrine

Aggregated Queries

🧪 Testing

🔧 Troubleshooting

"AggregatedQueryBuilder not injected"

Cause: Repository not configured as a service with auto-wiring.

Solution: Ensure repositories are in services.yaml:

"Relation 'xyz' not found"

Cause: Typo in relation name or relation not defined in entity.

Solution: Check entity's #[ORM\ManyToOne], #[ORM\OneToMany], etc. attributes.

Slow performance

Causes:

• Missing database indexes on foreign keys
• Too many relations (>10)
• Large collections without LIMIT

Solutions:

• Limit collections using filters (future feature)
• Use ->limit() on main query
• Use batching for large datasets

📚 Examples

See the /examples directory for complete working examples:

🤝 Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Add tests for new features
4. Ensure tests pass (composer test)
5. Check code style (composer cs:check)
6. Run static analysis (composer phpstan)
7. Commit your changes (git commit -m 'Add amazing feature')
8. Push to the branch (git push origin feature/amazing-feature)
9. Open a Pull Request

See CONTRIBUTING.md for detailed guidelines.

🔐 Security

If you discover a security vulnerability, please email:

📧 [email protected]

Do not create public issues for security vulnerabilities.

All security vulnerabilities will be promptly addressed.

📝 Changelog

See CHANGELOG.md for release history and migration guides.

🗺️ Roadmap

v1.1 (Q1 2025)

• ✨ ManyToMany support (belongsToMany)
• ✨ Nested relations (profile.company.country)
• ✨ Query result caching (Redis, Memcached)
• ✨ Conditional relation loading
• ✨ Relation callbacks support

v2.0 (Q2-Q3 2025)

• ✨ GraphQL-like query syntax
• ✨ Polymorphic relation support
• ✨ Advanced filtering DSL
• ✨ Performance monitoring integration
• ✨ DQL integration
• ✨ Automatic query optimization

Want a feature? Open an issue or vote on existing ones!

📄 License

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

👨‍💻 Credits

Author: Razmik Galstyan
GitHub: @rgalstyan
Email: [email protected]
LinkedIn: Razmik Galstyan

Inspired by Laravel Aggregated Queries and built to solve Doctrine's N+1 problem.

Built with ❤️ for the Symfony community.

🔗 Related Projects

• Laravel Aggregated Queries - Laravel version of this package
• Doctrine Issue #4762 - The N+1 problem we solve
• Doctrine ORM - The amazing ORM this bundle extends

💬 Support

• ⭐ Star the repo if you find it useful
• 🐛 Report bugs via GitHub Issues
• 💡 Request features via GitHub Issues
• 📖 Improve docs via Pull Requests
• 💬 Ask questions in GitHub Discussions
• 📣 Share with your team and on social media

🎯 Why This Bundle Exists

Doctrine's ORM is powerful but has a well-known N+1 problem with collections that fetch="EAGER" doesn't solve (documented since 2015).

This bundle provides a clean, performant solution using modern SQL's JSON aggregation capabilities:

• ✅ Reduces queries from 5–15 down to 1
• ✅ Up to 7x performance improvement
• ✅ 90% less memory usage
• ✅ Zero configuration needed
• ✅ Works with existing Doctrine entities

Perfect for: APIs, dashboards, mobile backends, and any read-heavy Symfony application.

🌟 Show Your Support

If this bundle saves you time and improves your app's performance, please:

• ⭐ Star the project on GitHub
• 📣 Share it with your team
• 💬 Write about your experience
• 🤝 Contribute improvements

Every star and contribution helps the project grow!