How to use subqueries in Eloquent in Laravel?

Laravel Eloquent supports subqueries in SELECT, FROM, WHERE, and ORDER BY clauses, enabling flexible data retrieval without raw SQL; 1. Use selectSub() to add computed columns like post count per user; 2. Use fromSub() or closure in from() to treat subquery as derived table for grouped data; 3. Use whereExists(), whereNotExists(), or scalar subqueries in where() for conditional filtering based on related data; 4. Pass subqueries directly to orderBy() to sort by values from related records, such as users ordered by latest post date; always prefer whereColumn() for column comparisons and consider performance impacts on large datasets by evaluating alternatives like joins or eager loading.

Using subqueries in Eloquent in Laravel allows you to embed one query inside another, which is useful when you need to filter, select, or order results based on data from related or the same tables. Laravel’s Eloquent ORM, built on top of the Query Builder, supports subqueries in SELECT, FROM, WHERE, and ORDER BY clauses starting from Laravel 5.6 .

Here’s how to use subqueries effectively in Eloquent:

1. Subquery in SELECT Clause

Use a subquery to add a calculated or aggregated column in your result.

use Illuminate\Database\Query\Expression;

$users = User::select('users.*', new Expression('(' . 
    Post::selectRaw('COUNT(*)')
        ->whereColumn('posts.user_id', 'users.id')
        ->toSql() 
    . ') as post_count'
))
->addBinding(Post::selectRaw('COUNT(*)')
    ->whereColumn('posts.user_id', 'users.id')
    ->getBindings(), 'select')
->get();

Or, better: use selectSub() for cleaner syntax:

$users = User::selectSub(
    Post::whereColumn('posts.user_id', 'users.id')->selectRaw('COUNT(*)'),
    'post_count'
)->get();

✅ This adds a post_count field to each user, counting their posts.

2. Subquery in FROM Clause (Subquery Joins)

Use a subquery as a derived table in the FROM clause.

$latestPosts = Post::select('user_id', DB::raw('MAX(created_at) as latest_post_date'))
    ->groupBy('user_id');

$users = User::fromSub($latestPosts, 'latest_posts')
    ->join('users', 'users.id', '=', 'latest_posts.user_id')
    ->get();

Alternatively, using from() with a closure:

$users = DB::table(function ($query) {
    $query->select('user_id', DB::raw('MAX(created_at) as last_post'))
          ->from('posts')
          ->groupBy('user_id');
}, 'latest_posts')
->join('users', 'users.id', '=', 'latest_posts.user_id')
->select('users.name', 'latest_posts.last_post')
->get();

✅ Useful when you need to work with grouped or filtered data as a base table.

3. Subquery in WHERE Clause

Use a subquery to compare values conditionally.

$users = User::where('id', '!=', function ($query) {
    $query->from('users')
          ->selectRaw('MAX(id)'); // Exclude the latest user
})->get();

More practical example: Get users whose latest post was over a month ago.

$users = User::whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('posts')
          ->whereColumn('posts.user_id', 'users.id')
          ->where('posts.created_at', '>', now()->subMonth());
})->get();

Or use a scalar subquery:

$lastLoginThreshold = now()->subDays(30);

$inactiveUsers = User::where(function ($query) use ($lastLoginThreshold) {
    $query->where('last_login', '<', $lastLoginThreshold)
          ->orWhereNull('last_login');
})
->where('id', 'in', function ($q) {
    $q->select('user_id')
      ->from('posts')
      ->where('created_at', '>', now()->subYear());
})->get();

✅ Use whereExists, whereNotExists, or scalar comparisons for conditional filtering.

4. Subquery in ORDER BY Clause

Order results based on a value from a related table using a subquery.

Example: Order users by the creation date of their latest post.

$users = User::orderByDesc(
    Post::select('created_at')
        ->whereColumn('posts.user_id', 'users.id')
        ->latest()
        ->limit(1)
)->get();

Or more explicitly:

$users = User::select('users.*')
    ->orderByDesc(
        Post::whereColumn('posts.user_id', 'users.id')
            ->selectRaw('MAX(created_at)')
    )
    ->get();

✅ This avoids joining and grouping, and works well with indexes.

Key Tips

• Always use whereColumn() when comparing columns between the main query and subquery.
• Use selectSub() and fromSub() for cleaner, more readable code.
• Be cautious with performance—subqueries can be slower on large datasets. Consider using joins or eager loading where appropriate.
• When using raw subqueries, don’t forget to bind parameters manually if needed (though Laravel often handles this).

Basically, Laravel makes subqueries manageable and expressive. Whether you're selecting, filtering, or sorting based on related data, subqueries in Eloquent give you the flexibility without dropping down to raw SQL in most cases.

The above is the detailed content of How to use subqueries in Eloquent in Laravel?. For more information, please follow other related articles on the PHP Chinese website!