PHP
Leveraging Subquery Selects and Where Exists for Advanced Eloquent Queries
Master complex data retrieval by incorporating subquery selects to add computed values and `whereExists` clauses for efficient conditional filtering in Eloquent.
use App\Models\Post;
use App\Models\Comment;
use App\Models\User;
// Assume Post and Comment models exist, and Comment has 'post_id' and 'is_approved' columns.
// Assume User and Login models exist, and Login has 'user_id' and 'created_at' columns.
// Example 1: Select posts and add a boolean column indicating if they have approved comments
$postsWithApprovedComments = Post::query()
->select('posts.*')
->selectSub(function ($query) {
$query->from('comments')
->selectRaw('count(*)')
->whereColumn('post_id', 'posts.id')
->where('is_approved', true);
}, 'approved_comments_count')
->whereExists(function ($query) {
$query->from('comments')
->whereColumn('post_id', 'posts.id')
->where('is_approved', true);
})
->get();
echo "Posts with Approved Comments:
";
foreach ($postsWithApprovedComments as $post) {
echo "- Post: '{$post->title}' (Approved Comments Count: {$post->approved_comments_count})
";
}
// Example 2: Add a subquery to select a specific value (e.g., last login time for users)
$usersWithLastLogin = User::addSelect(['last_login_at' => function ($query) {
$query->select('created_at')
->from('logins')
->whereColumn('user_id', 'users.id')
->latest()
->limit(1);
}])->get();
echo "
Users with Last Login:
";
foreach ($usersWithLastLogin as $user) {
echo "- User: '{$user->name}' (Last Login: " . ($user->last_login_at ? $user->last_login_at->format('Y-m-d H:i:s') : 'N/A') . ")
";
}
How it works: Eloquent's `selectSub` and `whereExists` methods are powerful tools for building complex queries that go beyond simple relationship joins. `selectSub` allows you to embed a subquery directly into your SELECT statement, adding a computed column to your results (e.g., `approved_comments_count`). The `whereExists` method, on the other hand, efficiently filters parent models based on the existence of related records that meet specific conditions, often performing better than a `WHERE IN` clause for large datasets by leveraging database optimizations. The `addSelect` method provides a convenient way to add a single subquery selection.