PHP
Selecting Related Data with Eloquent Subqueries for Performance
Enhance query performance and reduce N+1 problems by incorporating subqueries directly into your Eloquent `select` statements to retrieve related aggregate data.
<?php
use App\Models\Post;
use App\Models\Comment;
// Retrieve posts along with their latest comment's body and total comment count
$posts = Post::addSelect([
'latest_comment_body' => Comment::select('body')
->whereColumn('post_id', 'posts.id') // Correlate subquery to parent query
->latest()
->limit(1)
])
->addSelect([
'comments_count' => Comment::selectRaw('count(*)')
->whereColumn('post_id', 'posts.id')
])
->get();
foreach ($posts as $post) {
echo "Post: " . $post->title . "
";
echo " Latest Comment: " . ($post->latest_comment_body ?? 'N/A') . "
";
echo " Total Comments: " . $post->comments_count . "
";
}
How it works: This snippet demonstrates a powerful technique to include aggregated or specific related data directly in your main query using Eloquent subqueries. By leveraging `addSelect`, you can embed subqueries that fetch a single value (like the `latest_comment_body`) or an aggregate (like `comments_count`) for each parent record. This approach can significantly improve performance by avoiding the N+1 problem inherent in eager loading for single-value aggregates and reducing the need to load entire related collections.