Alaa Amer

Eloquent ORM Advanced Features: Mastering Laravel's Database Layer

Eloquent ORM Advanced Features: Mastering Laravel's Database Layer

Expert Guide by Alaa Amer – Professional Web Developer & Applications Designer

Laravel's Eloquent ORM provides powerful database abstraction. Master advanced features for complex applications, optimal performance, and elegant code architecture.

2️⃣ Advanced Query Techniques

Query Builder Mastery:

<?php
// app/Services/PostService.php
namespace App\Services;

use App\Models\Post;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Pagination\LengthAwarePaginator;

class PostService
{
    public function getPostsWithAdvancedFiltering(array $filters): LengthAwarePaginator
    {
        return Post::query()
            ->with([
                'user:id,name,avatar',
                'category:id,name,slug',
                'tags:id,name',
                'images' => function ($query) {
                    $query->where('collection', 'gallery')->limit(3);
                }
            ])
            ->withCount(['comments', 'likes'])
            ->withSum('views', 'count')
            ->when($filters['category'] ?? null, function ($query, $category) {
                $query->whereHas('category', function ($query) use ($category) {
                    $query->where('slug', $category);
                });
            })
            ->when($filters['tags'] ?? null, function ($query, $tags) {
                $query->whereHas('tags', function ($query) use ($tags) {
                    $query->whereIn('slug', (array) $tags);
                });
            })
            ->when($filters['author'] ?? null, function ($query, $author) {
                $query->whereHas('user', function ($query) use ($author) {
                    $query->where('username', $author);
                });
            })
            ->when($filters['search'] ?? null, function ($query, $search) {
                $query->where(function ($query) use ($search) {
                    $query->where('title', 'LIKE', "%{$search}%")
                          ->orWhere('content', 'LIKE', "%{$search}%")
                          ->orWhereFullText(['title', 'content'], $search);
                });
            })
            ->when($filters['date_range'] ?? null, function ($query, $dateRange) {
                [$start, $end] = explode(',', $dateRange);
                $query->whereBetween('published_at', [
                    Carbon::parse($start)->startOfDay(),
                    Carbon::parse($end)->endOfDay()
                ]);
            })
            ->published()
            ->latest('published_at')
            ->paginate(15);
    }

    public function getPopularPostsByCategory(): Collection
    {
        return Post::select([
                'posts.id',
                'posts.title',
                'posts.slug',
                'posts.published_at',
                'categories.name as category_name'
            ])
            ->selectRaw('
                (posts.views_count * 0.3 +
                 posts.likes_count * 0.5 +
                 posts.shares_count * 0.7 +
                 comments_count * 0.4) as popularity_score
            ')
            ->join('categories', 'posts.category_id', '=', 'categories.id')
            ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
            ->selectRaw('COUNT(comments.id) as comments_count')
            ->where('posts.status', 'published')
            ->groupBy([
                'posts.id', 'posts.title', 'posts.slug',
                'posts.published_at', 'categories.name',
                'posts.views_count', 'posts.likes_count', 'posts.shares_count'
            ])
            ->orderByDesc('popularity_score')
            ->limit(10)
            ->get();
    }

    public function getPostsWithComplexAggregations(): Collection
    {
        return Post::query()
            ->select(['id', 'title', 'user_id', 'category_id'])
            ->selectSub(function ($query) {
                $query->from('comments')
                      ->whereColumn('post_id', 'posts.id')
                      ->selectRaw('AVG(rating)');
            }, 'average_rating')
            ->selectSub(function ($query) {
                $query->from('post_views')
                      ->whereColumn('post_id', 'posts.id')
                      ->whereDate('viewed_at', '>=', now()->subDays(7))
                      ->selectRaw('COUNT(*)');
            }, 'weekly_views')
            ->withExists([
                'comments as has_recent_comments' => function ($query) {
                    $query->where('created_at', '>=', now()->subDays(3));
                }
            ])
            ->having('average_rating', '>', 4)
            ->orHaving('weekly_views', '>', 100)
            ->orderByDesc('weekly_views')
            ->get();
    }
}

Advanced Scopes and Local Scopes:

<?php
// app/Models/Post.php - Advanced Scopes
class Post extends Model
{
    // Simple Scopes
    public function scopePublished($query)
    {
        return $query->where('status', 'published')
                    ->whereNotNull('published_at')
                    ->where('published_at', '<=', now());
    }

    public function scopeFeatured($query)
    {
        return $query->where('is_featured', true);
    }

    // Parameterized Scopes
    public function scopeByStatus($query, $status)
    {
        return $query->where('status', $status);
    }

    public function scopePopular($query, $days = 7)
    {
        return $query->where('created_at', '>=', now()->subDays($days))
                    ->withCount(['views', 'comments', 'likes'])
                    ->orderByDesc('views_count');
    }

    public function scopeWithMinimumEngagement($query, $minViews = 100, $minComments = 5)
    {
        return $query->withCount(['comments', 'views'])
                    ->having('views_count', '>=', $minViews)
                    ->having('comments_count', '>=', $minComments);
    }

    // Complex Business Logic Scopes
    public function scopeForRecommendation($query, User $user)
    {
        $userCategories = $user->preferredCategories()->pluck('id');
        $userTags = $user->followedTags()->pluck('id');

        return $query->published()
                    ->where('user_id', '!=', $user->id)
                    ->where(function ($query) use ($userCategories, $userTags) {
                        $query->whereIn('category_id', $userCategories)
                              ->orWhereHas('tags', function ($query) use ($userTags) {
                                  $query->whereIn('tags.id', $userTags);
                              });
                    })
                    ->withCount(['views', 'comments', 'likes'])
                    ->orderByDesc('likes_count');
    }

    public function scopeTrending($query, $period = 'week')
    {
        $date = match ($period) {
            'day' => now()->subDay(),
            'week' => now()->subWeek(),
            'month' => now()->subMonth(),
            default => now()->subWeek(),
        };

        return $query->published()
                    ->where('published_at', '>=', $date)
                    ->withCount([
                        'comments as recent_comments_count' => function ($query) use ($date) {
                            $query->where('created_at', '>=', $date);
                        },
                        'likes as recent_likes_count' => function ($query) use ($date) {
                            $query->where('created_at', '>=', $date);
                        }
                    ])
                    ->orderByRaw('(recent_comments_count * 2 + recent_likes_count * 3) DESC');
    }
}

4️⃣ Performance Optimization

Query Optimization Techniques:

<?php
// app/Services/OptimizedQueryService.php
namespace App\Services;

class OptimizedQueryService
{
    // Avoid N+1 queries
    public function getPostsWithAuthors(): Collection
    {
        // Bad: N+1 Query Problem
        // $posts = Post::all();
        // foreach ($posts as $post) {
        //     echo $post->user->name; // N additional queries
        // }

        // Good: Eager Loading
        return Post::with([
            'user:id,name,email', // Select specific columns
            'category:id,name',
            'tags:id,name',
            'comments' => function ($query) {
                $query->latest()->limit(5);
            }
        ])->get();
    }

    // Lazy Eager Loading for conditional relationships
    public function loadConditionalRelations(Collection $posts): Collection
    {
        $posts->load([
            'user:id,name',
            'images' => function ($query) {
                $query->where('collection', 'featured');
            }
        ]);

        // Load comments only for featured posts
        $featuredPosts = $posts->where('is_featured', true);
        $featuredPosts->load(['comments.user:id,name']);

        return $posts;
    }

    // Chunking for large datasets
    public function processLargeDataset(): void
    {
        Post::published()
            ->with('user:id,name')
            ->chunkById(100, function ($posts) {
                foreach ($posts as $post) {
                    // Process each post
                    $this->updatePostMetrics($post);
                }
            });
    }

    // Cursor pagination for better performance
    public function getCursorPaginatedPosts(): CursorPaginator
    {
        return Post::published()
            ->with(['user:id,name', 'category:id,name'])
            ->orderBy('id')
            ->cursorPaginate(20);
    }

    // Raw queries for complex operations
    public function getPostStatistics(): Collection
    {
        return collect(DB::select("
            SELECT
                c.name as category_name,
                COUNT(p.id) as post_count,
                AVG(p.views_count) as avg_views,
                MAX(p.created_at) as latest_post
            FROM categories c
            LEFT JOIN posts p ON c.id = p.category_id
            WHERE p.status = 'published'
            GROUP BY c.id, c.name
            ORDER BY post_count DESC
        "));
    }

    // Subquery optimization
    public function getAuthorsWithPostStats(): Collection
    {
        return User::select('users.*')
            ->selectSub(
                Post::select(DB::raw('count(*)'))
                    ->whereColumn('user_id', 'users.id')
                    ->where('status', 'published'),
                'published_posts_count'
            )
            ->selectSub(
                Post::select(DB::raw('sum(views_count)'))
                    ->whereColumn('user_id', 'users.id')
                    ->where('status', 'published'),
                'total_views'
            )
            ->having('published_posts_count', '>', 0)
            ->orderByDesc('total_views')
            ->get();
    }
}

Advanced Caching Strategies:

<?php
// app/Services/PostCacheService.php
namespace App\Services;

class PostCacheService
{
    protected $cacheTime = 3600; // 1 hour

    public function getCachedPost(int $id): ?Post
    {
        return Cache::tags(['posts'])->remember(
            "post.{$id}",
            $this->cacheTime,
            fn () => Post::with(['user', 'category', 'tags'])->find($id)
        );
    }

    public function getCachedPopularPosts(): Collection
    {
        return Cache::tags(['posts', 'popular'])->remember(
            'posts.popular.weekly',
            $this->cacheTime * 6, // 6 hours
            function () {
                return Post::published()
                    ->with(['user:id,name', 'category:id,name'])
                    ->withCount(['comments', 'likes'])
                    ->orderByDesc('likes_count')
                    ->limit(10)
                    ->get();
            }
        );
    }

    public function invalidatePostCache(Post $post): void
    {
        Cache::tags(['posts'])->flush();
        Cache::forget("post.{$post->id}");

        // Invalidate related caches
        if ($post->is_featured) {
            Cache::tags(['popular', 'featured'])->flush();
        }
    }

    // Model-based cache invalidation
    public function setupCacheInvalidation(): void
    {
        Post::saved(function ($post) {
            $this->invalidatePostCache($post);
        });

        Post::deleted(function ($post) {
            $this->invalidatePostCache($post);
        });
    }
}

Next Steps

Master user security and access control with Authentication & Authorization in Laravel applications.

📩 Need help with Eloquent optimization?