Laravel Eloquent Advanced Techniques: Mastering Database Relations and Performance

Laravel's Eloquent ORM is powerful out of the box, but mastering its advanced features can transform how you handle complex database operations. From polymorphic relationships to custom query scopes, let's explore techniques that will elevate your Laravel applications to enterprise level.

Complex Relationship Patterns

Polymorphic Many-to-Many Relationships

One of Eloquent's most powerful features is polymorphic relationships. Let's build a comprehensive tagging system:

// Models setup
class Tag extends Model
{
    protected $fillable = ['name', 'slug', 'color'];

    public function posts()
    {
        return $this->morphedByMany(Post::class, 'taggable');
    }

    public function videos()
    {
        return $this->morphedByMany(Video::class, 'taggable');
    }

    public function products()
    {
        return $this->morphedByMany(Product::class, 'taggable');
    }

    // Get all tagged models regardless of type
    public function taggables()
    {
        return $this->hasMany(Taggable::class);
    }

    // Scope for finding tags by multiple types
    public function scopeForTypes($query, array $types)
    {
        return $query->whereHas('taggables', function ($q) use ($types) {
            $q->whereIn('taggable_type', $types);
        });
    }
}

class Post extends Model
{
    public function tags()
    {
        return $this->morphToMany(Tag::class, 'taggable');
    }

    public function syncTagsFromNames(array $tagNames)
    {
        $tags = collect($tagNames)->map(function ($name) {
            return Tag::firstOrCreate([
                'name' => $name,
                'slug' => Str::slug($name),
            ]);
        });

        $this->tags()->sync($tags->pluck('id'));
        
        return $this;
    }
}

// Usage examples
$post = Post::find(1);

// Attach tags by names
$post->syncTagsFromNames(['Laravel', 'PHP', 'Web Development']);

// Find all posts with specific tags
$posts = Post::whereHas('tags', function ($query) {
    $query->whereIn('slug', ['laravel', 'php']);
})->get();

// Get popular tags across all content types
$popularTags = Tag::withCount(['posts', 'videos', 'products'])
    ->having(DB::raw('posts_count + videos_count + products_count'), '>', 5)
    ->orderByDesc(DB::raw('posts_count + videos_count + products_count'))
    ->get();

Has-Many-Through with Intermediate Models

Handle complex relationships through multiple tables:

class Country extends Model
{
    public function users()
    {
        return $this->hasMany(User::class);
    }

    // Direct relationship to posts through users
    public function posts()
    {
        return $this->hasManyThrough(Post::class, User::class);
    }

    // More complex: get comments through posts through users
    public function comments()
    {
        return $this->through('posts')->has('comments');
    }

    // Custom has-many-through with additional constraints
    public function publishedPosts()
    {
        return $this->hasManyThrough(Post::class, User::class)
            ->whereNotNull('posts.published_at');
    }

    // Get statistics for dashboard
    public function getStatsAttribute()
    {
        return [
            'users_count' => $this->users()->count(),
            'posts_count' => $this->posts()->count(),
            'published_posts_count' => $this->publishedPosts()->count(),
            'comments_count' => $this->posts()->withCount('comments')->get()->sum('comments_count'),
        ];
    }
}

class User extends Model 
{
    public function country()
    {
        return $this->belongsTo(Country::class);
    }

    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

class Post extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

// Usage
$country = Country::with([
    'users.posts.comments',
    'posts.user'
])->find(1);

// Get all comments from a specific country
$comments = Comment::whereHas('post.user.country', function ($query) use ($countryId) {
    $query->where('id', $countryId);
})->get();

Advanced Query Builder Techniques

Custom Query Scopes and Builders

Create reusable, chainable query logic:

class PostQueryBuilder extends Builder
{
    public function published()
    {
        return $this->whereNotNull('published_at')
                   ->where('published_at', '<=', now());
    }

    public function byAuthor($authorId)
    {
        return $this->where('user_id', $authorId);
    }

    public function withCategory($category)
    {
        return $this->whereHas('categories', function ($query) use ($category) {
            $query->where('slug', $category);
        });
    }

    public function popular($days = 30)
    {
        return $this->where('created_at', '>=', now()->subDays($days))
                   ->withCount(['views', 'likes', 'comments'])
                   ->orderByDesc(DB::raw('views_count + likes_count * 2 + comments_count * 3'));
    }

    public function trending()
    {
        return $this->select('posts.*')
                   ->selectRaw('
                       (
                           COALESCE(views_count, 0) * 1 +
                           COALESCE(likes_count, 0) * 3 +
                           COALESCE(comments_count, 0) * 5 +
                           (CASE 
                               WHEN created_at >= ? THEN 10 
                               WHEN created_at >= ? THEN 5 
                               ELSE 0 
                           END)
                       ) as trend_score
                   ', [now()->subDays(1), now()->subWeek()])
                   ->leftJoin('post_statistics', 'posts.id', '=', 'post_statistics.post_id')
                   ->orderByDesc('trend_score');
    }

    public function searchContent($term)
    {
        return $this->where(function ($query) use ($term) {
            $query->where('title', 'LIKE', "%{$term}%")
                  ->orWhere('content', 'LIKE', "%{$term}%")
                  ->orWhere('excerpt', 'LIKE', "%{$term}%")
                  ->orWhereHas('tags', function ($q) use ($term) {
                      $q->where('name', 'LIKE', "%{$term}%");
                  });
        });
    }

    public function withFullStats()
    {
        return $this->withCount([
            'views',
            'likes',
            'comments',
            'comments as recent_comments_count' => function ($query) {
                $query->where('created_at', '>=', now()->subDays(7));
            }
        ])->with([
            'user:id,name,avatar',
            'categories:id,name,slug',
            'tags:id,name,slug,color'
        ]);
    }
}

class Post extends Model
{
    public function newEloquentBuilder($query)
    {
        return new PostQueryBuilder($query);
    }

    // Local scopes for simple conditions
    public function scopeScheduled($query)
    {
        return $query->whereNotNull('published_at')
                    ->where('published_at', '>', now());
    }

    public function scopeByStatus($query, $status)
    {
        return match($status) {
            'published' => $query->published(),
            'scheduled' => $query->scheduled(),
            'draft' => $query->whereNull('published_at'),
            default => $query,
        };
    }

    // Complex scope with subqueries
    public function scopeWithPopularityScore($query)
    {
        return $query->addSelect([
            'popularity_score' => PostStatistic::selectRaw(
                'views_count * 1 + likes_count * 3 + comments_count * 5'
            )->whereColumn('post_id', 'posts.id')
        ]);
    }
}

// Usage examples
$posts = Post::published()
            ->byAuthor(1)
            ->withCategory('technology')
            ->popular()
            ->withFullStats()
            ->paginate(15);

$trendingPosts = Post::trending()
                   ->published()
                   ->take(10)
                   ->get();

$searchResults = Post::searchContent('Laravel')
                   ->published()
                   ->withPopularityScore()
                   ->orderByDesc('popularity_score')
                   ->paginate(20);

Advanced Aggregations and Window Functions

Leverage database features for complex analytics:

class AnalyticsService 
{
    public function getUserGrowthStats($months = 12)
    {
        return User::select(
            DB::raw('DATE_FORMAT(created_at, "%Y-%m") as month'),
            DB::raw('COUNT(*) as new_users'),
            DB::raw('SUM(COUNT(*)) OVER (ORDER BY DATE_FORMAT(created_at, "%Y-%m")) as cumulative_users')
        )
        ->where('created_at', '>=', now()->subMonths($months))
        ->groupBy(DB::raw('DATE_FORMAT(created_at, "%Y-%m")'))
        ->orderBy('month')
        ->get();
    }

    public function getPostEngagementTrends()
    {
        return Post::select(
            'posts.*',
            DB::raw('ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as user_post_rank'),
            DB::raw('AVG(views_count) OVER (PARTITION BY user_id) as avg_user_views'),
            DB::raw('LAG(views_count) OVER (ORDER BY created_at) as prev_post_views')
        )
        ->join('post_statistics', 'posts.id', '=', 'post_statistics.post_id')
        ->where('posts.created_at', '>=', now()->subMonths(3))
        ->get();
    }

    public function getTopPerformingContent($limit = 10)
    {
        return Post::select(
            'posts.*',
            DB::raw('
                RANK() OVER (
                    ORDER BY (views_count * 1 + likes_count * 3 + comments_count * 5) DESC
                ) as performance_rank
            '),
            DB::raw('views_count * 1 + likes_count * 3 + comments_count * 5 as engagement_score'),
            DB::raw('
                PERCENT_RANK() OVER (
                    ORDER BY (views_count * 1 + likes_count * 3 + comments_count * 5)
                ) as percentile_rank
            ')
        )
        ->join('post_statistics', 'posts.id', '=', 'post_statistics.post_id')
        ->whereNotNull('published_at')
        ->orderByDesc('engagement_score')
        ->limit($limit)
        ->get();
    }

    public function getCategoryPerformance()
    {
        return Category::select(
            'categories.*',
            DB::raw('COUNT(posts.id) as posts_count'),
            DB::raw('AVG(post_statistics.views_count) as avg_views'),
            DB::raw('SUM(post_statistics.views_count) as total_views'),
            DB::raw('
                CASE 
                    WHEN AVG(post_statistics.views_count) > (
                        SELECT AVG(views_count) FROM post_statistics
                    ) THEN "Above Average"
                    ELSE "Below Average"
                END as performance_category
            ')
        )
        ->leftJoin('category_post', 'categories.id', '=', 'category_post.category_id')
        ->leftJoin('posts', 'category_post.post_id', '=', 'posts.id')
        ->leftJoin('post_statistics', 'posts.id', '=', 'post_statistics.post_id')
        ->groupBy('categories.id', 'categories.name', 'categories.slug')
        ->having('posts_count', '>', 0)
        ->orderByDesc('avg_views')
        ->get();
    }
}

Performance Optimization Strategies

Eager Loading Optimization

Prevent N+1 queries with sophisticated loading strategies:

class PostService
{
    public function getPostsForDashboard($perPage = 15)
    {
        return Post::with([
            // Basic relationships
            'user:id,name,email,avatar',
            'categories:id,name,slug,color',
            
            // Conditional loading
            'comments' => function ($query) {
                $query->latest()->limit(3)->with('user:id,name,avatar');
            },
            
            // Count relationships without loading
            'tags:id,name,slug',
        ])
        ->withCount([
            'views',
            'likes',
            'comments',
            'comments as recent_comments_count' => function ($query) {
                $query->where('created_at', '>=', now()->subDays(7));
            }
        ])
        ->withExists([
            'likes as user_liked' => function ($query) {
                $query->where('user_id', auth()->id());
            }
        ])
        ->published()
        ->latest()
        ->paginate($perPage);
    }

    public function getPostWithOptimizedComments(Post $post)
    {
        // Load post with paginated comments
        $post->load([
            'user:id,name,avatar',
            'categories:id,name,slug',
            'tags:id,name,slug,color'
        ]);

        // Separate query for comments with pagination
        $comments = $post->comments()
                         ->with(['user:id,name,avatar', 'replies.user:id,name,avatar'])
                         ->latest()
                         ->paginate(20);

        return compact('post', 'comments');
    }

    // Batch loading for multiple posts
    public function loadStatsForPosts($posts)
    {
        $postIds = $posts->pluck('id');

        // Single query to get all statistics
        $stats = DB::table('post_statistics')
                  ->whereIn('post_id', $postIds)
                  ->get()
                  ->keyBy('post_id');

        // Single query to get all recent activity
        $recentActivity = DB::table('post_views')
                           ->whereIn('post_id', $postIds)
                           ->where('created_at', '>=', now()->subDays(7))
                           ->groupBy('post_id')
                           ->selectRaw('post_id, COUNT(*) as recent_views')
                           ->get()
                           ->keyBy('post_id');

        // Attach data to posts
        $posts->each(function ($post) use ($stats, $recentActivity) {
            $post->statistics = $stats[$post->id] ?? null;
            $post->recent_views = $recentActivity[$post->id]->recent_views ?? 0;
        });

        return $posts;
    }
}

class OptimizedPostController extends Controller
{
    public function index(Request $request)
    {
        $posts = Post::when($request->category, function ($query, $category) {
                return $query->whereHas('categories', fn($q) => $q->where('slug', $category));
            })
            ->when($request->tag, function ($query, $tag) {
                return $query->whereHas('tags', fn($q) => $q->where('slug', $tag));
            })
            ->when($request->search, function ($query, $search) {
                return $query->searchContent($search);
            })
            ->with(['user:id,name,avatar', 'categories:id,name,slug'])
            ->withCount(['views', 'likes', 'comments'])
            ->published()
            ->latest()
            ->paginate(15);

        // Eager load additional data in batches
        if ($posts->count() > 0) {
            $this->loadAdditionalPostData($posts);
        }

        return response()->json([
            'posts' => $posts,
            'filters' => $request->only(['category', 'tag', 'search']),
        ]);
    }

    private function loadAdditionalPostData($posts)
    {
        $postIds = $posts->getCollection()->pluck('id');

        // Load user engagement in single query
        $userEngagement = DB::table('post_likes')
                           ->whereIn('post_id', $postIds)
                           ->where('user_id', auth()->id())
                           ->pluck('post_id')
                           ->flip();

        $posts->getCollection()->each(function ($post) use ($userEngagement) {
            $post->user_liked = isset($userEngagement[$post->id]);
        });
    }
}

Database Indexing and Query Optimization

// Migration with proper indexes
class CreateOptimizedPostsTable extends Migration
{
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->onDelete('cascade');
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('excerpt')->nullable();
            $table->longText('content');
            $table->string('featured_image')->nullable();
            $table->timestamp('published_at')->nullable();
            $table->timestamps();

            // Compound indexes for common queries
            $table->index(['published_at', 'created_at']); // For published posts ordered by date
            $table->index(['user_id', 'published_at']); // For user's published posts
            $table->index(['created_at', 'user_id']); // For user's recent posts
            
            // Full-text search index
            $table->fullText(['title', 'content', 'excerpt']);
        });
    }
}

class Post extends Model
{
    // Use full-text search when available
    public function scopeFullTextSearch($query, $term)
    {
        return $query->whereFullText(['title', 'content', 'excerpt'], $term);
    }

    // Fallback search method
    public function scopeBasicSearch($query, $term)
    {
        return $query->where(function ($q) use ($term) {
            $q->where('title', 'LIKE', "%{$term}%")
              ->orWhere('excerpt', 'LIKE', "%{$term}%");
        });
    }

    // Smart search that uses full-text when possible
    public function scopeSmartSearch($query, $term)
    {
        if (config('database.default') === 'mysql') {
            return $query->fullTextSearch($term);
        }
        
        return $query->basicSearch($term);
    }
}

Advanced Model Patterns

Custom Collections and Model Factories

class PostCollection extends Collection
{
    public function published()
    {
        return $this->filter(fn ($post) => $post->isPublished());
    }

    public function byAuthor($authorId)
    {
        return $this->filter(fn ($post) => $post->user_id == $authorId);
    }

    public function groupByMonth()
    {
        return $this->groupBy(fn ($post) => $post->created_at->format('Y-m'));
    }

    public function calculateEngagementMetrics()
    {
        return [
            'total_views' => $this->sum('views_count'),
            'total_likes' => $this->sum('likes_count'),
            'total_comments' => $this->sum('comments_count'),
            'average_engagement' => $this->avg(function ($post) {
                return $post->views_count + $post->likes_count * 3 + $post->comments_count * 5;
            }),
        ];
    }

    public function getPopularTags($limit = 10)
    {
        return $this->pluck('tags')
                   ->flatten()
                   ->countBy('name')
                   ->sortDesc()
                   ->take($limit);
    }
}

class Post extends Model
{
    public function newCollection(array $models = [])
    {
        return new PostCollection($models);
    }
    
    // Custom attributes
    public function getEngagementScoreAttribute()
    {
        return ($this->views_count ?? 0) + 
               ($this->likes_count ?? 0) * 3 + 
               ($this->comments_count ?? 0) * 5;
    }

    public function getReadingTimeAttribute()
    {
        $wordCount = str_word_count(strip_tags($this->content));
        return ceil($wordCount / 200); // Average reading speed
    }

    public function getExcerptOrContentAttribute()
    {
        if ($this->excerpt) {
            return $this->excerpt;
        }

        return Str::limit(strip_tags($this->content), 150);
    }

    // Model events for cache management
    protected static function booted()
    {
        static::saved(function ($post) {
            Cache::tags(['posts', "user:{$post->user_id}"])->flush();
        });

        static::deleted(function ($post) {
            Cache::tags(['posts', "user:{$post->user_id}"])->flush();
        });
    }
}

// Usage examples
$posts = Post::published()->get(); // Returns PostCollection
$metrics = $posts->calculateEngagementMetrics();
$popularTags = $posts->getPopularTags(5);
$monthlyPosts = $posts->groupByMonth();

Repository Pattern with Caching

interface PostRepositoryInterface
{
    public function findBySlug(string $slug): ?Post;
    public function getPublished(int $perPage = 15);
    public function getByAuthor(int $authorId, int $perPage = 15);
    public function search(string $term, int $perPage = 15);
    public function getPopular(int $days = 30, int $limit = 10);
}

class CachedPostRepository implements PostRepositoryInterface
{
    private $cacheTime = 3600; // 1 hour

    public function findBySlug(string $slug): ?Post
    {
        return Cache::tags(['posts'])->remember(
            "post.slug.{$slug}",
            $this->cacheTime,
            fn () => Post::with(['user', 'categories', 'tags'])->where('slug', $slug)->first()
        );
    }

    public function getPublished(int $perPage = 15)
    {
        $page = request('page', 1);
        $cacheKey = "posts.published.page.{$page}.perPage.{$perPage}";
        
        return Cache::tags(['posts'])->remember(
            $cacheKey,
            $this->cacheTime,
            fn () => Post::published()
                         ->with(['user', 'categories'])
                         ->withCount(['views', 'likes', 'comments'])
                         ->latest()
                         ->paginate($perPage)
        );
    }

    public function getByAuthor(int $authorId, int $perPage = 15)
    {
        $page = request('page', 1);
        $cacheKey = "posts.author.{$authorId}.page.{$page}";
        
        return Cache::tags(['posts', "user:{$authorId}"])->remember(
            $cacheKey,
            $this->cacheTime,
            fn () => Post::where('user_id', $authorId)
                         ->published()
                         ->with(['categories', 'tags'])
                         ->latest()
                         ->paginate($perPage)
        );
    }

    public function search(string $term, int $perPage = 15)
    {
        $page = request('page', 1);
        $cacheKey = "posts.search." . md5($term) . ".page.{$page}";
        
        return Cache::remember(
            $cacheKey,
            300, // 5 minutes for search results
            fn () => Post::smartSearch($term)
                         ->published()
                         ->with(['user', 'categories'])
                         ->paginate($perPage)
        );
    }

    public function getPopular(int $days = 30, int $limit = 10)
    {
        $cacheKey = "posts.popular.days.{$days}.limit.{$limit}";
        
        return Cache::tags(['posts'])->remember(
            $cacheKey,
            $this->cacheTime,
            fn () => Post::popular($days)
                         ->with(['user', 'categories'])
                         ->take($limit)
                         ->get()
        );
    }

    public function invalidateCache(Post $post = null)
    {
        if ($post) {
            Cache::tags(['posts', "user:{$post->user_id}"])->flush();
        } else {
            Cache::tags(['posts'])->flush();
        }
    }
}

class PostService
{
    public function __construct(
        private PostRepositoryInterface $postRepository
    ) {}

    public function getPostsForHomepage()
    {
        return [
            'featured' => $this->postRepository->getPopular(7, 3),
            'recent' => $this->postRepository->getPublished(8),
            'popular' => $this->postRepository->getPopular(30, 5),
        ];
    }

    public function createPost(array $data): Post
    {
        $post = Post::create([
            ...$data,
            'slug' => $this->generateUniqueSlug($data['title']),
            'user_id' => auth()->id(),
        ]);

        $this->postRepository->invalidateCache();

        return $post;
    }

    private function generateUniqueSlug(string $title): string
    {
        $slug = Str::slug($title);
        $count = Post::where('slug', 'like', "{$slug}%")->count();
        
        return $count ? "{$slug}-" . ($count + 1) : $slug;
    }
}

Testing Advanced Eloquent Features

class PostTest extends TestCase
{
    use RefreshDatabase;

    public function test_post_with_tags_relationship()
    {
        $post = Post::factory()->create();
        $tags = Tag::factory()->count(3)->create();
        
        $post->tags()->attach($tags);
        
        $this->assertCount(3, $post->fresh()->tags);
        
        foreach ($tags as $tag) {
            $this->assertTrue($post->tags->contains($tag));
        }
    }

    public function test_popular_scope()
    {
        // Create posts with different engagement levels
        $popularPost = Post::factory()->create(['created_at' => now()->subDays(1)]);
        $regularPost = Post::factory()->create(['created_at' => now()->subDays(1)]);
        $oldPost = Post::factory()->create(['created_at' => now()->subDays(60)]);

        // Create statistics
        PostStatistic::factory()->create([
            'post_id' => $popularPost->id,
            'views_count' => 1000,
            'likes_count' => 50,
            'comments_count' => 20,
        ]);

        PostStatistic::factory()->create([
            'post_id' => $regularPost->id,
            'views_count' => 100,
            'likes_count' => 5,
            'comments_count' => 2,
        ]);

        $popularPosts = Post::popular(30)->get();
        
        $this->assertTrue($popularPosts->first()->is($popularPost));
        $this->assertCount(2, $popularPosts); // oldPost excluded by date
    }

    public function test_custom_collection_methods()
    {
        $posts = Post::factory()->count(5)->create();
        
        // Test custom collection methods
        $collection = $posts->fresh(); // Get PostCollection
        $this->assertInstanceOf(PostCollection::class, $collection);
        
        $metrics = $collection->calculateEngagementMetrics();
        $this->assertArrayHasKey('total_views', $metrics);
    }

    public function test_repository_caching()
    {
        Cache::shouldReceive('tags')->with(['posts'])->andReturnSelf();
        Cache::shouldReceive('remember')->once()->andReturn(collect());
        
        $repository = new CachedPostRepository();
        $posts = $repository->getPublished();
        
        // Second call should use cache
        $cachedPosts = $repository->getPublished();
    }
}

Conclusion

Mastering advanced Eloquent techniques transforms your Laravel applications from simple CRUD operations to sophisticated, high-performance systems. The patterns covered in this article—from complex relationships to performance optimization strategies—are essential for building scalable enterprise applications.

Key takeaways:

• Complex relationships enable rich data modeling
• Custom query builders create reusable, chainable query logic
• Advanced aggregations provide powerful analytics capabilities
• Performance optimization ensures scalability as your data grows
• Repository patterns with caching improve maintainability and speed
• Custom collections add domain-specific behavior to your models

These techniques will help you build Laravel applications that not only work well but scale gracefully and perform efficiently under real-world conditions. Start implementing these patterns gradually in your projects—the investment in learning advanced Eloquent will pay dividends in cleaner, more maintainable code.