Download the PHP package shammaa/laravel-optimized-queries without Composer

Laravel Optimized Queries

Transform 5-15 Eloquent queries into a single optimized SQL statement using JSON aggregation. Reduce database calls, improve response time, and keep Eloquent's clean syntax.

✨ Features

• 🚀 Single SQL Query — Combines relations into one statement via JSON aggregation
• 🔍 Auto-Detection — Automatically detects relation types (BelongsTo, HasMany, BelongsToMany, etc.)
• 📊 Aggregate Subqueries — withSum(), withAvg(), withMin(), withMax()
• 🔗 Nested Relations — author.profile.company
• 🎯 Conditional Chaining — when(), unless(), tap(), tapQuery()
• 🌍 Translation Support — Auto-integration with shammaa/laravel-model-translations
• 💾 Built-in Caching — Request cache + external cache with tags
• 🛡️ Safe Mode — Falls back to standard Eloquent if query fails
• 📖 Pagination — paginate() and simplePaginate()
• 📦 Chunking — chunk() and lazy() for large datasets
• 🔧 Debugging — toSql(), dump(), debug(), showPerformance()
• 🗄️ Multi-Database — MySQL, MariaDB, PostgreSQL, SQLite

📦 Installation

Publish configuration (optional):

🚀 Quick Start

1. Add the Trait

2. Write Queries

That's it! The package auto-detects that author is BelongsTo, category is BelongsTo, and comments is HasMany.

📖 Usage Guide

Loading Relations

Counting Relations

Aggregate Subqueries

WHERE Conditions

Conditional Chaining

Build queries dynamically based on conditions:

Scoped Queries

Create optimized queries with pre-applied conditions:

Nested Relations

Searching

Ordering

📄 Pagination & Large Datasets

Pagination

Chunking

Lazy Collections

🌍 Translation Support

Automatic integration with shammaa/laravel-model-translations:

💾 Caching

Cache auto-clears when models are saved or deleted.

🔧 Output Formats

🔧 Retrieval Methods

🕵️ Debugging

🔀 Using the Facade

⚙️ Configuration

Environment Variables

🛡️ Safe Mode

When safe_mode is enabled (default), the package automatically falls back to standard Eloquent if the optimized query encounters any issue:

⚡ Performance

How It Works

Traditional Eloquent eager loading executes one query per relation:

This package combines everything into a single query using JSON subqueries:

Real Numbers

Best Practices

🏗️ Large-Scale Sites (E-Commerce, High Traffic)

For large datasets (100k+ records, 8+ relations), enable query splitting and timeout protection:

How query splitting works:

Why this matters:

• ❌ Without splitting: 1 massive SQL with 10 subqueries → timeout / memory crash
• ✅ With splitting: 2-3 smaller SQL queries → fast and stable

🔍 When to Use

✅ Perfect For

• API Endpoints — Reduce response time
• Admin Dashboards — Complex data with multiple relations
• Mobile Backends — Low latency matters
• Listings / DataTables — 3-10 relations per record
• Read-Heavy Services — 90%+ reads
• High-Traffic Pages — Every millisecond counts

⚠️ Consider Standard Eloquent For

• Write Operations — Use standard Eloquent for creates/updates
• Model Events — Default format is arrays (no model events)
• Deep Nesting — More than 3 levels of nested relations

🤝 Real-World Example: Homepage

Result: 3 queries total instead of 15-20+. With caching: 0 queries after first visit.

🐛 Troubleshooting

JSON Functions Not Supported

Your database must support JSON functions:

• MySQL 5.7+ / MariaDB 10.5+
• PostgreSQL 9.4+
• SQLite 3.38+

Query Returns Empty Relations

Make sure your model has the relation method defined:

Slow Queries

1. Add database indexes on foreign keys
2. Use ->select() to limit columns
3. Use ->paginate() or ->limit()
4. Enable caching with ->cache(3600)

Cache Not Clearing

Cache auto-clears on model saved and deleted events. For manual clearing:

Laravel Octane / Long-Running Processes

The in-memory request cache clears automatically when models are saved/deleted. To clear manually:

📝 Requirements

• PHP 8.1+
• Laravel 9.x, 10.x, 11.x, or 12.x
• MySQL 5.7+ / MariaDB 10.5+ / PostgreSQL 9.4+ / SQLite 3.38+

📄 License

MIT License. See LICENSE file.

👤 Author

Shadi Shammaa — [email protected]

⭐ Support

If this package saved you time, please give it a star on GitHub! Every star helps the package reach more developers.