PHP
Using Eloquent Subquery Selects for Advanced Aggregations
Optimize complex queries by embedding subqueries directly into your Laravel Eloquent selects to retrieve related aggregate data or latest values efficiently in a single query.
// Example: Get posts and the title of their latest comment
use App\Models\Post;
use App\Models\Comment;
use Illuminate\Support\Facades\DB;
// Assuming Post hasMany Comments
// Comments have 'post_id', 'content', 'created_at'
$postsWithLatestCommentTitle = Post::query()
->select([
'posts.*', // Select all post columns
'latest_comment_content' => Comment::select('content')
->whereColumn('post_id', 'posts.id') // Correlate subquery to parent query
->latest() // Order by created_at DESC
->limit(1) // Get only the latest one
])
->get();
foreach ($postsWithLatestCommentTitle as $post) {
echo "Post: {$post->title} (Latest Comment: {$post->latest_comment_content})
";
}
// Example 2: Selecting average rating for a product from its reviews
use App\Models\Product;
use App\Models\Review;
// Assuming Product hasMany Reviews
// Reviews have 'product_id', 'rating'
$productsWithAverageRating = Product::query()
->select([
'products.*',
'average_rating' => Review::selectRaw('AVG(rating)')
->whereColumn('product_id', 'products.id')
])
->get();
foreach ($productsWithAverageRating as $product) {
echo "Product: {$product->name} (Average Rating: {$product->average_rating})
";
}
How it works: Laravel Eloquent's subquery selects allow you to embed complex subqueries directly into the `SELECT` clause of your main query. This is incredibly useful for retrieving related aggregate data (like average ratings) or specific values (like the content of the latest comment) without performing additional queries or complex joins that might duplicate rows. You use `select()` with an array where keys are the desired alias and values are the subquery builders. The `whereColumn()` method is crucial for correlating the subquery to the main query's table, ensuring the subquery operates on the correct related record.