PHPackages                             shammaa/laravel-optimized-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. shammaa/laravel-optimized-queries

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

shammaa/laravel-optimized-queries
=================================

Advanced Laravel query optimizer - Reduce multiple Eloquent queries to a single optimized SQL statement with JSON aggregation. Supports nested relations, callbacks, belongsToMany, polymorphic relations, and caching.

v1.5.1(4mo ago)132MITPHPPHP ^8.1

Since Dec 16Pushed 4mo agoCompare

[ Source](https://github.com/shammaa/laravel-optimized-queries)[ Packagist](https://packagist.org/packages/shammaa/laravel-optimized-queries)[ RSS](/packages/shammaa-laravel-optimized-queries/feed)WikiDiscussions main Synced 3w ago

READMEChangelogDependencies (4)Versions (23)Used By (0)

Laravel Optimized Queries
=========================

[](#laravel-optimized-queries)

[![Latest Version](https://camo.githubusercontent.com/110378192c3b1b806602e13061c364c48444bf0fc2802eb8fefeb4e6968a77ac/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7368616d6d61612f6c61726176656c2d6f7074696d697a65642d717565726965732e737667)](https://packagist.org/packages/shammaa/laravel-optimized-queries)[![Total Downloads](https://camo.githubusercontent.com/32ce41026aad97fc32b28ad1be79fa0c2b53606fbc826b892e11a596af387be4/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f7368616d6d61612f6c61726176656c2d6f7074696d697a65642d717565726965732e737667)](https://packagist.org/packages/shammaa/laravel-optimized-queries)[![License](https://camo.githubusercontent.com/6e3c484f41918badc857ee369daf979304235d2471566f6e903777e7b40bd43b/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f6c2f7368616d6d61612f6c61726176656c2d6f7074696d697a65642d717565726965732e737667)](https://packagist.org/packages/shammaa/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.

```
// ❌ Traditional: 4 queries
$articles = Article::with(['author', 'category', 'comments'])->get();

// ✅ Optimized: 1 query — same result, 5-10x faster
$articles = Article::optimized()
    ->with('author')
    ->with('category')
    ->with('comments')
    ->get();
```

---

✨ Features
----------

[](#-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
--------------

[](#-installation)

```
composer require shammaa/laravel-optimized-queries
```

Publish configuration (optional):

```
php artisan vendor:publish --provider="Shammaa\LaravelOptimizedQueries\LaravelOptimizedQueriesServiceProvider"
```

---

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

[](#-quick-start)

### 1. Add the Trait

[](#1-add-the-trait)

```
use Shammaa\LaravelOptimizedQueries\Traits\HasOptimizedQueries;

class Article extends Model
{
    use HasOptimizedQueries;

    protected $fillable = ['title', 'slug', 'content', 'user_id', 'category_id'];
}
```

### 2. Write Queries

[](#2-write-queries)

```
// Basic — loads author + category + comments in ONE query
$articles = Article::optimized()
    ->with('author')
    ->with('category')
    ->with('comments')
    ->where('published', true)
    ->latest()
    ->limit(20)
    ->get();
```

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

---

📖 Usage Guide
-------------

[](#-usage-guide)

### Loading Relations

[](#loading-relations)

```
// Single relation
->with('author')

// Select specific columns
->with('author', ['id', 'name', 'avatar'])

// Multiple relations
->with(['author', 'category', 'comments'])

// Multiple with columns
->with([
    'author' => ['id', 'name'],
    'category' => ['id', 'name', 'slug'],
    'comments'
])

// With callback filter
->with(['comments' => fn($q) => $q->where('approved', true)->latest()])

// With columns + callback
->with(['comments' => [
    'columns' => ['id', 'body', 'created_at'],
    'callback' => fn($q) => $q->latest()->take(5)
]])
```

### Counting Relations

[](#counting-relations)

```
$articles = Article::optimized()
    ->with('author')
    ->withCount('comments')
    ->withCount('likes')
    ->get();

// Result: each article has 'comments_count' and 'likes_count'
```

### Aggregate Subqueries

[](#aggregate-subqueries)

```
$products = Product::optimized()
    ->with('category')
    ->withSum('orderItems', 'quantity')    // sum_orderItems_quantity
    ->withAvg('reviews', 'rating')         // avg_reviews_rating
    ->withMin('variants', 'price')         // min_variants_price
    ->withMax('variants', 'price')         // max_variants_price
    ->get();
```

### WHERE Conditions

[](#where-conditions)

```
->where('published', true)
->where('views', '>', 100)
->whereIn('category_id', [1, 2, 3])
->whereNotNull('published_at')
->whereBetween('price', [10, 50])
->whereDate('created_at', '>', '2025-01-01')
->whereHas('comments', fn($q) => $q->where('approved', true))
->whereDoesntHave('reports')
```

### Conditional Chaining

[](#conditional-chaining)

Build queries dynamically based on conditions:

```
$articles = Article::optimized()
    ->with('author')
    ->when($request->category_id, fn($q) => $q->where('category_id', $request->category_id))
    ->when($request->search, fn($q) => $q->search($request->search, ['title', 'content']))
    ->unless($user->isAdmin(), fn($q) => $q->where('published', true))
    ->latest()
    ->paginate(20);
```

### Scoped Queries

[](#scoped-queries)

Create optimized queries with pre-applied conditions:

```
$activeProducts = Product::scopedOptimized(
    fn($q) => $q->where('active', true)->where('stock', '>', 0)
)
    ->with('category')
    ->with('images')
    ->latest()
    ->get();
```

### Nested Relations

[](#nested-relations)

```
$articles = Article::optimized()
    ->with('author.profile')       // Nested: author -> profile
    ->with('category')
    ->get();
```

### Searching

[](#searching)

```
// Search in model columns
$results = Article::optimized()
    ->with('author')
    ->search('laravel', ['title', 'content'])
    ->get();

// Search in related model
$results = Article::optimized()
    ->with('author')
    ->searchRelation('comments', 'great', ['body'])
    ->get();
```

### Ordering

[](#ordering)

```
->orderBy('created_at', 'desc')
->orderByDesc('views')
->latest()                          // = orderByDesc('created_at')
->oldest()                          // = orderBy('created_at')
->inRandomOrder()
```

---

📄 Pagination & Large Datasets
---------------------------------

[](#-pagination--large-datasets)

### Pagination

[](#pagination)

```
// Standard pagination (with total count)
$articles = Article::optimized()
    ->with('author')
    ->paginate(20);

// Simple pagination (faster — no count query)
$articles = Article::optimized()
    ->with('author')
    ->simplePaginate(20);
```

### Chunking

[](#chunking)

```
// Process large datasets in chunks
Article::optimized()
    ->with('author')
    ->where('published', true)
    ->chunk(500, function ($articles) {
        foreach ($articles as $article) {
            // process...
        }
    });
```

### Lazy Collections

[](#lazy-collections)

```
// Memory-efficient iteration
Article::optimized()
    ->with('author')
    ->lazy(1000)
    ->each(function ($article) {
        // process one at a time...
    });
```

---

🌍 Translation Support
---------------------

[](#-translation-support)

Automatic integration with `shammaa/laravel-model-translations`:

```
// Load with specific locale
$articles = Article::optimized()
    ->with('category')
    ->locale('ar')
    ->get();

// Search in translations
$articles = Article::optimized()
    ->searchTranslation('لارافيل', ['title', 'content'], 'ar')
    ->get();

// Filter by translation
$articles = Article::optimized()
    ->whereTranslation('title', 'LIKE', '%Laravel%', 'en')
    ->get();

// Order by translation field
$articles = Article::optimized()
    ->orderByTranslation('title', 'asc', 'ar')
    ->get();

// Find by translated slug
$article = Article::optimized()
    ->with('author')
    ->whereTranslatedSlug('my-article-slug', 'en')
    ->first();
```

---

💾 Caching
---------

[](#-caching)

```
// Cache for 1 hour
$articles = Article::optimized()
    ->with('author')
    ->cache(3600)
    ->get();

// Cache with tags (Redis/Memcached)
$articles = Article::optimized()
    ->with('author')
    ->cache(3600, ['articles', 'homepage'])
    ->get();

// Custom cache key
$articles = Article::optimized()
    ->with('author')
    ->cacheKey('homepage_articles')
    ->cache(7200)
    ->get();

// Bypass cache
$articles = Article::optimized()
    ->with('author')
    ->withoutCache()
    ->get();
```

Cache auto-clears when models are saved or deleted.

---

🔧 Output Formats
----------------

[](#-output-formats)

```
// Arrays (default — fastest)
$articles = Article::optimized()->with('author')->get();

// Eloquent models
$articles = Article::optimized()->with('author')->asEloquent()->get();

// stdClass objects
$articles = Article::optimized()->with('author')->asObject()->get();

// Explicit format
$articles = Article::optimized()->with('author')->get('eloquent');
```

---

🔧 Retrieval Methods
-------------------

[](#-retrieval-methods)

```
// Get all matching records
->get()

// Get first record
->first()

// Get first or throw 404
->firstOrFail()

// Find by ID
->find(1)

// Find by ID or throw 404
->findOrFail(1)

// Find by slug (with translations)
->findBySlug('my-article')
->findBySlugOrFail('my-article')

// Count
->count()

// Check existence
->exists()
->doesntExist()

// Get single column value
->value('title')

// Pluck column
->pluck('title')
->pluck('title', 'id')

// API-ready response
->toApi()
```

---

🕵️ Debugging
------------

[](#️-debugging)

```
// See the generated SQL
$sql = Article::optimized()->with('author')->toSql();

// Dump SQL + bindings
Article::optimized()->with('author')->dump();

// Die & dump
Article::optimized()->with('author')->dd();

// Log to Laravel log
Article::optimized()->with('author')->debug()->get();

// Performance monitoring
$articles = Article::optimized()
    ->with('author')
    ->with('comments')
    ->get();

// Show performance after get()
Article::optimized()->with('author')->showPerformance();
```

---

🔀 Using the Facade
------------------

[](#-using-the-facade)

```
use Shammaa\LaravelOptimizedQueries\Facades\OptimizedQuery;

// From model class
$articles = OptimizedQuery::from(Article::class)
    ->with('author')
    ->get();

// From existing query
$query = Article::where('published', true);
$articles = OptimizedQuery::query($query)
    ->with('author')
    ->get();
```

---

⚙️ Configuration
----------------

[](#️-configuration)

```
// config/optimized-queries.php

return [
    'max_limit' => 1000,                    // Safety limit for records
    'default_format' => 'array',             // 'array', 'eloquent', 'object'
    'enable_cache' => env('OPTIMIZED_QUERIES_CACHE', true),
    'default_cache_ttl' => env('OPTIMIZED_QUERIES_CACHE_TTL', 3600),
    'cache_prefix' => 'optimized_queries:',
    'enable_query_logging' => env('OPTIMIZED_QUERIES_LOG', false),
    'enable_performance_monitoring' => env('OPTIMIZED_QUERIES_PERFORMANCE_MONITORING', false),
    'safe_mode' => env('OPTIMIZED_QUERIES_SAFE_MODE', true),
    'max_relations_per_query' => env('OPTIMIZED_QUERIES_MAX_RELATIONS', 0),
    'query_timeout' => env('OPTIMIZED_QUERIES_TIMEOUT', 0),
    'supported_drivers' => ['mysql', 'mariadb', 'pgsql', 'sqlite'],
    'json_function' => 'auto',
];
```

### Environment Variables

[](#environment-variables)

VariableDefaultDescription`OPTIMIZED_QUERIES_CACHE``true`Enable/disable caching`OPTIMIZED_QUERIES_CACHE_TTL``3600`Default cache TTL (seconds)`OPTIMIZED_QUERIES_LOG``false`Log generated SQL queries`OPTIMIZED_QUERIES_PERFORMANCE_MONITORING``false`Enable performance tracking`OPTIMIZED_QUERIES_SAFE_MODE``true`Fallback to Eloquent on failure`OPTIMIZED_QUERIES_MAX_RELATIONS``0`Max relations per query (0 = unlimited)`OPTIMIZED_QUERIES_TIMEOUT``0`Query timeout in seconds (0 = no limit)---

🛡️ Safe Mode
------------

[](#️-safe-mode)

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

```
// If the optimized query fails, it silently falls back to Eloquent
// A warning is logged for debugging
$articles = Article::optimized()
    ->with('author')
    ->get(); // Always returns results, never crashes

// Disable safe mode for debugging
$articles = Article::optimized()
    ->with('author')
    ->safeMode(false)
    ->get(); // Will throw exception on failure
```

---

⚡ Performance
-------------

[](#-performance)

### How It Works

[](#how-it-works)

Traditional Eloquent eager loading executes **one query per relation**:

```
SELECT * FROM articles WHERE published = 1           -- 1 query
SELECT * FROM users WHERE id IN (1, 2, 3, ...)       -- 2 queries
SELECT * FROM categories WHERE id IN (...)           -- 3 queries
SELECT * FROM comments WHERE article_id IN (...)     -- 4 queries

```

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

```
SELECT
    articles.*,
    (SELECT JSON_OBJECT('id', users.id, 'name', users.name)
     FROM users WHERE users.id = articles.user_id LIMIT 1) AS author,
    (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', comments.id, 'body', comments.body)), ']')
     FROM comments WHERE comments.article_id = articles.id) AS comments
FROM articles
WHERE articles.published = 1
```

### Real Numbers

[](#real-numbers)

MetricTraditionalOptimizedImprovementSQL Queries4-15**1**-93%Response Time150-400ms**25-60ms**5-10x fasterMemory UsageHigh**Lower**~40% lessDatabase ConnectionsMultiple**Single**-93%### Best Practices

[](#best-practices)

```
// ✅ Always paginate for lists
->paginate(20)

// ✅ Select only needed columns
->with('author', ['id', 'name'])

// ✅ Use cache for repeated queries
->cache(3600)

// ✅ Limit results
->limit(100)

// ✅ Use chunk() for background processing
->chunk(500, fn($batch) => ...)
```

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

[](#️-large-scale-sites-e-commerce-high-traffic)

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

```
# .env — recommended for large sites
OPTIMIZED_QUERIES_MAX_RELATIONS=5
OPTIMIZED_QUERIES_TIMEOUT=10
OPTIMIZED_QUERIES_SAFE_MODE=true
```

**How query splitting works:**

```
// You request 10 relations
Product::optimized()
    ->with('category')
    ->with('brand')
    ->with('images')
    ->with('variants')
    ->with('reviews')
    ->with('tags')
    ->with('attributes')
    ->with('seller')
    ->withCount('orders')
    ->withAvg('reviews', 'rating')
    ->get();

// With max_relations_per_query=5, it automatically splits into:
// Query 1: base data + category + brand + images + variants + reviews
// Query 2: tags + attributes + seller (by IDs from query 1)
// Then merges the results — you don't notice any difference!
```

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

[](#-when-to-use)

### ✅ Perfect For

[](#-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

[](#️-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
------------------------------

[](#-real-world-example-homepage)

```
class HomepageController extends Controller
{
    public function index()
    {
        // Latest articles — 1 query instead of 4
        $articles = Article::optimized()
            ->with(['author' => ['id', 'name', 'avatar'], 'category' => ['id', 'name', 'slug']])
            ->withCount('comments')
            ->where('published', true)
            ->latest()
            ->limit(10)
            ->cache(3600)
            ->get();

        // Featured products — 1 query instead of 5
        $products = Product::optimized()
            ->with(['category', 'images' => ['id', 'url']])
            ->withCount('reviews')
            ->withAvg('reviews', 'rating')
            ->withMin('variants', 'price')
            ->where('featured', true)
            ->limit(8)
            ->cache(1800)
            ->get();

        // Categories with counts — 1 query
        $categories = Category::optimized()
            ->withCount('products')
            ->where('is_visible', true)
            ->orderBy('sort_order')
            ->cache(7200)
            ->get();

        return view('homepage', compact('articles', 'products', 'categories'));
    }
}
```

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

---

🐛 Troubleshooting
-----------------

[](#-troubleshooting)

### JSON Functions Not Supported

[](#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

[](#query-returns-empty-relations)

Make sure your model has the relation method defined:

```
class Article extends Model
{
    public function author()
    {
        return $this->belongsTo(User::class, 'user_id');
    }
}
```

### Slow Queries

[](#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-not-clearing)

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

```
$article = Article::find(1);
$article->clearOptimizedCache();
```

### Laravel Octane / Long-Running Processes

[](#laravel-octane--long-running-processes)

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

```
OptimizedQueryBuilder::clearRequestCache();
```

---

📝 Requirements
--------------

[](#-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
---------

[](#-license)

MIT License. See [LICENSE](LICENSE) file.

---

👤 Author
--------

[](#-author)

**Shadi Shammaa** —

---

⭐ Support
---------

[](#-support)

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

###  Health Score

39

—

LowBetter than 85% of packages

Maintenance75

Regular maintenance activity

Popularity10

Limited adoption so far

Community6

Small or concentrated contributor base

Maturity53

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 ~3 days

Total

22

Last Release

135d ago

### Community

Maintainers

![](https://avatars.githubusercontent.com/u/5834300?v=4)[shadishammaa](/maintainers/shadishammaa)[@shadishammaa](https://github.com/shadishammaa)

---

Top Contributors

[![shammaa](https://avatars.githubusercontent.com/u/8601466?v=4)](https://github.com/shammaa "shammaa (40 commits)")

---

Tags

searchlaravelperformanceeloquentoptimizationquery builderfull text searchdatabase-optimizationquery-optimizationperformance-monitoringfast searchjson-aggregationn1-problem

###  Code Quality

TestsPHPUnit

### Embed Badge

![Health badge](/badges/shammaa-laravel-optimized-queries/health.svg)

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

###  Alternatives

[kirschbaum-development/eloquent-power-joins

The Laravel magic applied to joins.

1.6k29.9M42](/packages/kirschbaum-development-eloquent-power-joins)[psalm/plugin-laravel

Psalm plugin for Laravel

3345.1M337](/packages/psalm-plugin-laravel)[cybercog/laravel-love

Make Laravel Eloquent models reactable with any type of emotions in a minutes!

1.2k322.4k1](/packages/cybercog-laravel-love)[reedware/laravel-relation-joins

Adds the ability to join on a relationship by name.

2121.2M16](/packages/reedware-laravel-relation-joins)[itpathsolutions/dbstan

Database Standardization and Analysis Tool for Laravel

442.1k](/packages/itpathsolutions-dbstan)

PHPackages © 2026

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