How to Add a Where Clause to a Relationship Query in Laravel

When working with Laravel’s Eloquent ORM, it’s common to retrieve models along with their related models. Often, we need to add conditions to these related models’ data. Fortunately, Laravel ships with built-in features that allow us to filter them accordingly.

When using Eloquent relationships, we can filter based on the related records’ information with either whereHas or whereRelation. While equivalent, whereRelation supports a shorter syntax without using a closure for adding condition(s). Note: whereRelation requires Laravel 8 or higher.

In this article, we’ll explore both techniques to add a where clause to Eloquent relationships. Additionally, we’ll cover applying methods like with(), withCount() and doesntHave() to further refine the database queries. Let’s dive right in!

Step 1: Create and Run the Migrations

First, create the migration for the models:

php artisan make:migration create_posts_table
php artisan make:migration create_comments_table

Modify the migration files as follows:

For the posts table migration use:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->text('content');
            $table->boolean('published')->default(false);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('posts');
    }
};

For the comments table migration use:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('post_id');
            $table->text('content');
            $table->boolean('approved')->default(false);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('comments');
    }
};

Now run the migrations using the following artisan command:

php artisan migrate

Step 2: Create the Models

Create the Post and Comment models using the following commands:

php artisan make:model Post
php artisan make:model Comment

Step 3: Add the Model Code

Define the Models with their Eloquent relationships and any additional methods.

For the Post Model use:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $fillable = ['title', 'content', 'published'];

    // Define the "comments" relationship
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }

    // Additional methods and relationships can be defined here
}

For the Comment Model use:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Comment extends Model
{
    protected $fillable = ['post_id', 'content', 'approved'];

    // Define the "post" relationship
    public function post()
    {
        return $this->belongsTo(Post::class);

        // Additional methods and relationships can be defined here
    }
}

Step 4: Insert Some Test Data

Before proceeding to steps 7 and 8, let’s insert some test data to work with. First make sure to generate a fresh autoload file using composer by running:

composer dump-autoload

Then open Artisan Tinker by running:

php artisan tinker

In the Tinker prompt, copy & paste the following PHP code:

$post = Post::create([
    'title'     => 'Sample Post',
    'content'   => 'This is a sample post.',
    'published' => true
]);
$comment1 = Comment::create([
    'post_id' => $post->id,
    'content' => 'Great post!', 'approved' => true
]);
$comment2 = Comment::create([
    'post_id' => $post->id,
    'content' => 'I enjoyed reading this.', 'approved' => false
]);

You now have the following data in your database:

Step 5: Query Related Models Using whereHas

Now that the test data is inserted, you can query related models based on conditions using whereHas.

Let’s open the tinker prompt again by running:

php artisan tinker

In the Tinker prompt, copy & paste the following PHP code:

$publishedComments = Comment::whereHas('post', function ($query) {
    $query->where('published', true);
})->get();

When you run that code you’ll see Laravel fetches both our comments, as expected:

Step 6: Query Related Models Using whereRelation (Laravel 8+)

Equivalently, you can also query related models based on conditions using the shorthand whereRelation method. Try it out by running, for example in tinker:

$publishedComments = Comment::whereRelation('post', 'published', '=', true)->get();

As you can see this is a lot more readable since it doesn’t require a closure but achieves the same result as we got before when using whereHas.

That’s it you’ve learned to use both whereHas and whereRelation to query based on conditions on related models! Read on to learn about a few more ways to filter related models using Eloquent.

Advanced Techniques for Filtering Related Models

Using withCount to get Models Plus a Related Model Count

For scenarios where you require a model along with a count of related records meeting certain conditions, use withCount:

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

Using with() to get Related Models (Prevents Extra Query)

Fetching related models often results in additional queries, but you can mitigate this using the with() method. It eagerly loads the related models along with the main query, reducing the number of database queries you’d need if you access relationships later on.

$postsWithApprovedComments = Post::with(['comments' => function ($query) {
    $query->where('approved', true);
}])->get();

Using doesntHave to Query Where a Relation is Empty (null)

If you want to retrieve parent models that don’t have any related records, you can use the doesntHave method. It’s especially useful for situations like identifying posts without any comments, as this example shows:

$postsWithoutComments = Post::doesntHave('comments')->get();

Combining whereHas with orWhereHas to Filter on Related Data

We can chain together whereHas and orWhereHas to create more advanced queries that involve conditions on two related Models. Consider the example below on how this can be applied.

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

$posts = Post::with(['comments', 'tags']);

$searchTerm = 'example';

$posts = $posts->whereHas('comments', function (Builder $query) use ($searchTerm) {
    $query->where('content', 'like', '%' . $searchTerm . '%');
})->orWhereHas('tags', function (Builder $query) use ($searchTerm) {
    $query->where('name', 'like', '%' . $searchTerm . '%');
});

dd($posts->get());

Note: To follow along with this example you’d need to a Model Tag and add tags relation to the Model Post. To limit the scope of this post to the essentials I’ve omitted the steps needed to create them.

Handle Edge Cases with whereHasMorph and whereMorphRelation

In scenarios involving polymorphic relationships, whereHasMorph and whereMorphRelation come into play. They allow you to filter models based on relationships with multiple types of related models, offering flexibility in querying.

$taggedItems = Item::whereHasMorph('taggable', [Tag::class], function ($query) {
    $query->where('name', 'like', 'laravel%');
})->get();

Conclusion

Adding a where clause to Laravel Eloquent relationships empowers you to retrieve models based on specific conditions applied to their related models. The introduction of whereRelation in Laravel 8 adds convenience and highlights Laravel’s continuous evolution in terms of developer experience.

Should you have any follow-up questions or additions to share, please feel free to engage in the comments section. Happy coding!

References

• Laravel Eloquent Relationships (Official Documentation)
• Laravel Query Builder (Official Documentation)