Mastering Subqueries in Laravel Eloquent & Query Builder

Laravel provides a powerful query builder and Eloquent ORM that allow developers to build complex SQL queries in a clean and expressive way. One of the lesser-known yet highly powerful features is subqueries.

In this article, we'll explore how to use subqueries in Laravel using both the Query Builder and Eloquent, with practical examples.

🔍 What is a Subquery?

A subquery is a SQL query nested inside another query. It's used to return data that will be used in the main query as a condition or value. Example in raw SQL:

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

🛠️ Using Subqueries in Laravel Query Builder

1. Where In Subquery

use Illuminate\Support\Facades\DB;

$users = DB::table('users')
    ->whereIn('id', function ($query) {
        $query->select('user_id')
              ->from('orders')
              ->where('status', 'completed');
    })
    ->get();

This is equivalent to the SQL shown above.

2. Select with Subquery as Column

$users = DB::table('users')
    ->select('name')
    ->selectSub(function ($query) {
        $query->from('orders')
              ->selectRaw('count(*)')
              ->whereColumn('orders.user_id', 'users.id');
    }, 'orders_count')
    ->get();

In this case, each user record will contain an orders_count field representing the number of orders they have.

3. Join with Subquery

$latestOrders = DB::table('orders')
    ->select('user_id', DB::raw('MAX(created_at) as last_order_date'))
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestOrders, 'latest_orders', function ($join) {
        $join->on('users.id', '=', 'latest_orders.user_id');
    })
    ->select('users.*', 'latest_orders.last_order_date')
    ->get();

Here, you're joining a subquery that calculates the latest order date per user.

🧠 Using Subqueries in Eloquent

1. Select Subquery in Eloquent

use App\Models\User;
use Illuminate\Support\Facades\DB;

$users = User::select('name')
    ->selectSub(function ($query) {
        $query->from('orders')
              ->selectRaw('count(*)')
              ->whereColumn('orders.user_id', 'users.id');
    }, 'orders_count')
    ->get();

This allows you to stay within Eloquent while using subqueries effectively.

2. Eloquent with Exists Subquery

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

The whereExists method is helpful when you just need to check for the presence of related data.

✅ Tips

• Use selectSub, joinSub, whereIn, whereExists, addSelect() for adding subqueries in different parts of the query.
• You can always fall back to raw queries using DB::raw() when needed.