Recently, Laravel 11 launched with a new structure and new features.

Post.php

    Here in class, we will define relationships with the category table two times. First, we define the relationship with the category table and then again we reuse it in another one and add our where clause to the category table and add the relevant name so we can use it again easily, and from one place we can manage it and our controller will look good. 

    Then we define out whereIn clause in our custom scope function so we can make it human-readable and again it can be helpful for reuse across the application.

<?php
            
namespace AppModels;

use IlluminateDatabaseEloquentModel;

class Post extends Model
{
    const STATUS_ACTIVE = 1;

    public function category(){
        return $this->belongsTo( Category::class );
    }

    public function activeCategory(){
        return $this->category()->active();
    }

    public function scopeActive( $query ){
        return $query->where('status', self::STATUS_ACTIVE);
    }

    public function scopeFilterCategories( $query ){
        return $query->whereIn('category_id', Category::active()->select('id'));
    }
}

Category.php

<?php

namespace AppModels;

use IlluminateDatabaseEloquentModel;

class Category extends Model
{
    const STATUS_ACTIVE = 1;

    public function scopeActive( $query ){
        return $query->where('status', self::STATUS_ACTIVE);
    }
}

Now, we will fetch the posts two ways. 

The first one is
using WhereHas function of the Eloquent Model where it uses the exists clause in our SQL
query.

Post::whereHas('activeCategory')->Active()->get();

It will generate an SQL query that looks like this:

select * from `posts` where exists (select * from `categories` where `posts`.`category_id` = `categories`.`id` and `status` = 1) and `status` = 1;

And now we EXPLAIN this query inside MySQL and get the report.

As explained in the report, first, it will check on the Posts table using a status index.

table: posts
Rows: 483
Filtered: 100.0
Extra: Using Where

And then it checks on the categories table,

table: categories
rows: 1
filtered: 56.00
Extra: Using index condition; Using where

Now we use the second way and that is Where In clause. Our Laravel code will look like,

Post::filterCategories()->Active()->get();

where filterCategories is the custom scope that we have defined inside our Post model class.

Now, Laravel runs the above code and generates out SQL query that will look like:

select * from `posts` where `category_id` in (select `id` from `categories` where `status` = 1) and `status` = 1;

As explained in the report, first, it will check on the Categories table and then the Posts table.

table: categories
rows: 28
filtered: 100.0
Extra: NULL

-----------------------

table: posts
Rows: 11
Filtered: 48.30
Extra: Using index condition; Using where    

Conclusions from the above, I think here, "where in" is checking for fewer records inside tables rows and can be fast in comparison to the whereHas ( EXISTS ).

But let me note that here we have 1000 posts and only 50 categories in our tables. And filtered records are 250 only. If there are many records in the categories table then maybe it Exists works more faster than wherein.