Advanced Subqueries & Relationship Queries

Hello! In our last lesson, we explored the power of polymorphic relationships for building flexible data structures. We even got a sneak peek at querying based on related models using whereHasMorph.

Today, we're diving much deeper into that concept. As you build more complex applications, you'll frequently need to retrieve data based on conditions in related tables. You might want to find all users who have made a purchase in the last month, or display a list of blog posts with their respective comment counts, all without killing your application's performance.

This lesson focuses on exactly that. Our learning outcome is to utilize subqueries and advanced relationship queries (whereHas, withCount) for complex lookups. We will cover how to filter your main model based on its relationships, how to efficiently count related models, and finally, how to use powerful subqueries to pull in specific data from related tables in a single, optimized database request.

Mastering these techniques is a huge step toward your goal of writing highly optimized database queries in Laravel.

1. Filtering Based on Relationship Existence: has() and whereHas()

The simplest question you can ask about a relationship is: "Does it exist?" For example, you might want to find all blog posts that have at least one comment.

Eloquent provides two primary methods for this:

• has('relation'): Retrieves models that have at least one related model.
• whereHas('relation', $callback): Retrieves models where the related models meet certain criteria defined in the $callback.

Using has() for Simple Existence Checks

Let's say you want to get all Post models that have comments. Instead of fetching all posts and then filtering them in PHP, you can do it at the database level.

use App\Models\Post;

// Retrieve all posts that have at least one comment.
$postsWithComments = Post::has('comments')->get();

// You can also specify the number of related models.
// Retrieve all posts that have three or more comments.
$postsWithManyComments = Post::has('comments', '>=', 3)->get();

Using whereHas() for Constrained Checks

whereHas() is more powerful. It allows you to add query constraints to the relationship check. For instance, let's find all posts that have comments containing the word "awesome".

use App\Models\Post;
use Illuminate\Database\Eloquent\Builder;

$posts = Post::whereHas('comments', function (Builder $query) {
    $query->where('content', 'like', '%awesome%');
})->get();

This is incredibly useful for building sophisticated search and filter features.

So what's happening "under the hood"? When you use has or whereHas, Eloquent isn't using a JOIN. Instead, it's generating a WHERE EXISTS subquery, which is often more efficient.

As the video shows, the generated query looks something like this:

SELECT *
FROM `posts`
WHERE EXISTS (
    SELECT *
    FROM `comments`
    WHERE `posts`.`id` = `comments`.`post_id`
      AND `content` LIKE '%awesome%'
)

This is a powerful concept because it keeps the main query clean and lets the database efficiently check for the existence of related records.

2. Counting Related Models: withCount()

Another common requirement is to display the count of related models. For example, showing Post (15 comments).

The naive approach is to loop through your posts and call $post->comments->count(). This creates a classic N+1 query problem: one query to get the posts, and then N additional queries to get the comments for each post.

The correct, optimized way is to use withCount(). This method adds a {relation}_count attribute to your model results using an efficient subquery.

use App\Models\Post;

$posts = Post::withCount('comments')->get();

foreach ($posts as $post) {
    // The 'comments_count' attribute is automatically available
    echo $post->title . ' (' . $post->comments_count . ' comments)';
}

Like whereHas, you can also add constraints to withCount to count only specific related models. For example, counting only approved comments.

use Illuminate\Database\Eloquent\Builder;

$posts = Post::withCount(['comments' => function (Builder $query) {
    $query->where('is_approved', true);
}])->get();

The SQL generated by withCount is a subquery in the SELECT clause:

SELECT `posts`.*,
       (SELECT count(*)
        FROM `comments`
        WHERE `posts`.`id` = `comments`.`post_id`) AS `comments_count`
FROM `posts`

This is extremely efficient, retrieving all the required information in a single database query.

3. Advanced Lookups with Eloquent Subqueries

whereHas and withCount are fantastic, but what if you need more? What if you want to sort your posts by their most recent comment date? Or display the amount of each employee's latest sale directly in an employee list?

This is where manual subqueries using addSelect, orderBy, and where come into play. This is one of the most powerful optimization techniques in Eloquent, as it allows you to get highly specific related data without loading entire relationship models into memory.

The Problem: Performance vs. Convenience

Let's take the example of showing a list of users and their last login date.
A User hasMany Login records.

• N+1 Approach: $user->logins()->latest()->first() inside a loop. Terrible performance.
• Eager Loading Approach: User::with('logins')->get(). This solves N+1 but can cause massive memory issues if users have thousands of login records. You're loading every login record for every user just to get one date from each.

The optimal solution is to tell the database to fetch only the data you need—the created_at timestamp of the latest login—and attach it to the user record.

The Subquery Solution

The article "Dynamic relationships in Laravel using subqueries" by Jonathan Reinink is the definitive guide on this topic. It masterfully explains the problem and the solution. We will walk through it together.

As the article explains, the core idea is to build a subquery that fetches the single piece of data you need and give it an alias. Laravel's query builder makes this remarkably clean:

$users = User::query()
    ->addSelect(['last_login_at' => Login::select('created_at')
        ->whereColumn('user_id', 'users.id') // This links the subquery to the outer query
        ->latest()
        ->take(1)
    ])
    ->get();

// Now you can access it directly!
$user->last_login_at;

This performs a single, efficient query and avoids loading any Login models into memory.

Bonus: "Dynamic" Relationships

The article goes on to show an even more advanced technique: using a subquery to select a foreign key (last_login_id) and then using a standard belongsTo relationship to eager-load just that one specific related model. This gives you the performance of a subquery with the convenience of a full Eloquent model.

This video from Laravel Daily provides a great visual summary of the performance benefits of using a subquery over a standard relationship for this exact use case.

Conclusion

You've now added some of the most powerful query tools in the Eloquent ORM to your arsenal. Let's recap the progression:

• has() and whereHas(): Filter models based on the existence of related records using efficient WHERE EXISTS subqueries.
• withCount(): Add aggregate counts to your models without the overhead of loading the relationships, using a SELECT (COUNT(*)) subquery.
• Manual Subqueries (addSelect): Achieve maximum performance and minimal memory usage by selecting specific aggregate data (like latest/oldest record attributes) from a related table.

Each of these tools allows you to push more computational work to the database, where it can be handled most efficiently. By choosing the right tool for the job, you can build complex features that remain fast and scalable.

Next Up:

We've mentioned the N+1 problem several times today. In the next lesson, we will do a deep dive specifically into this common performance pitfall. You will learn not only how to spot it but also how to resolve it using various eager loading strategies, including with() for simple cases and constrained eager loading for more complex scenarios.