How do I handle complex subqueries with Eloquent joins

Using Subqueries in Selects and Joins with Eloquent

1. Subselects in the Select Clause

You can add a subquery as a column in your main query using `addSelect()` with a subquery builder. For example, to get users along with the timestamp of their latest post:

php
$users = User::query()
    ->addSelect([
        'last_post_created_at' => Post::query()
            ->select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
    ])
    ->get();

This adds a subselect that fetches the latest post date for each user, accessible via `$user->last_post_created_at`[1].

Alternatively, you can use a raw expression with `DB::raw()` if you prefer:

php
use Illuminate\Support\Facades\DB;

$users = User::query()
    ->select('users.*')
    ->addSelect(DB::raw('(SELECT created_at FROM posts WHERE posts.user_id = users.id ORDER BY created_at DESC LIMIT 1) as last_post_created_at'))
    ->get();

2. Subqueries in Where Clauses

To filter records based on a subquery, use `whereRaw()` or `where()` with a raw expression. For example, to get users with at least 5 posts:

php
$users = User::query()
    ->whereRaw('(SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) >= 5')
    ->get();

Or using `where()` with `DB::raw()`:

php
use Illuminate\Support\Facades\DB;

$users = User::query()
    ->where(DB::raw('(SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id)'), '>=', 5)
    ->get();

This embeds the subquery in the `WHERE` clause[1].

3. Using `joinSub` for Joining with Subqueries

Eloquent supports joining subqueries using `joinSub()`, `leftJoinSub()`, or `rightJoinSub()`. This is useful when you want to join aggregated or filtered data as a derived table.

Example: Join users with their latest post timestamp:

php
use Illuminate\Database\Query\JoinClause;

$latestPosts = DB::table('posts')
    ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
    ->where('is_published', true)
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestPosts, 'latest_posts', function (JoinClause $join) {
        $join->on('users.id', '=', 'latest_posts.user_id');
    })
    ->get();

This joins the users table with a subquery that gets the latest published post per user[9].

4. Complex Joins with Conditions

You can specify advanced join conditions using closures with the `join()` method, allowing you to combine multiple conditions or use `where` clauses inside the join:

php
DB::table('users')
    ->join('contacts', function (JoinClause $join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->where('contacts.is_active', '=', 1);
    })
    ->get();

This approach lets you add complex constraints on join clauses[9].

5. Using Eloquent Relationships with `whereHas` for Subqueries

Sometimes, complex subqueries can be handled elegantly with Eloquent relationships and the `whereHas` method:

php
$orders = Order::query()
    ->whereHas('products', function ($query) {
        $query->where('quantity', '>', 10);
    })
    ->get();

This fetches orders that have related products meeting certain conditions, effectively using a subquery under the hood[4].

6. Creating Models from Subqueries

For very complex scenarios, you can create an Eloquent model that uses a subquery as its "table". This allows encapsulating complex joins or filters inside a model:

php
class AdminUser extends Model
{
    public function getTable()
    {
        $sql = User::query()
            ->select('id', 'name')
            ->where('admin_user', true)
            ->toRawSql();

        return DB::raw("({$sql}) as admin_users");
    }
}

// Usage
$admins = AdminUser::query()->get();

This technique treats a subquery as a virtual table, enabling you to use Eloquent methods on complex joined or filtered data sets[5][6].

Best Practices and Tips

- Avoid SQL Injection: When using raw expressions, always be cautious with parameter binding to avoid injection vulnerabilities[1].
- Use Query Builder for Complex Joins: Sometimes, the Eloquent ORM syntax can be cumbersome for very complex joins; using the query builder (`DB::table()`) can be more straightforward[8].
- Optimize Subqueries: Use indexes and limit subquery data to improve performance.
- Leverage Eloquent Relationships: When possible, use relationships and `whereHas` to keep queries readable and maintainable.
- Use `joinSub` for Derived Tables: This is a clean way to join aggregated or filtered data as a subquery without resorting to raw SQL.

By combining these techniques-subselects in selects and where clauses, `joinSub` for subquery joins, advanced join conditions, and Eloquent relationships-you can effectively handle complex subqueries with Eloquent joins in Laravel.

Citations:
[1] https://laraveldaily.com/post/laravel-eloquent-subquery-subselect-examples
[2] https://laracasts.com/discuss/channels/eloquent/how-to-make-this-complex-eloquent-subquery
[3] https://stackoverflow.com/questions/75407627/laravel-eloquent-complex-subquery
[4] https://www.linkedin.com/pulse/laravel-subqueries-eloquent-shashika-nuwan-r5eqc
[5] https://dev.to/bedram-tamang/eloquent-trick-laravel-model-from-subquery-4im6
[6] https://blog.jobins.jp/eloquent-trick-laravel-model-from-subquery
[7] https://www.bacancytechnology.com/qanda/laravel/laravel-subquery-where-in
[8] https://www.reddit.com/r/laravel/comments/2cn8rh/eloquent_more_trouble_than_its_worth/
[9] https://laravel.com/docs/12.x/queries