How to Filter Data Using Relational Model in Laravel ?

In this article i will show you to filter data using the relational model in laravel. Using the laravel relationship between the models we can easily fetch relative data but sometimes we want to fetch the data by filtering the child model that affect the result of parent model. You may want to filter the data with function so that you can only get the exact results you want. In MySQL we can do this by using inner join and same we can do in laravel using the join.

In this article we will take simple example of articles and users. We will filter the Users who has posted one article at least since register the account. To implement this example we will use hasMany and manyToOne relationship and that will filter the users data based on articles.

To accomplish this we will use has, whereHas and innerJoin in laravel

Here is the simple syntax

Model::has("RelativeModel",">",0)->get();

//OR

Model::whereHas(["RelativeModel"=>function($query){
  return $query->where("status",'1');
}])->get();

//OR

Model::join("realtive_table_name","realtive_table_name.model_primary_id","Model.id")
      ->where("status","1")->with("RelativeModel")->select("model.*")->get();

In the above statements we have used has , whereHas and innerJoin to filter the result from child model.

In first statement we count the number of relative model and getting the result on that condition. In second statement we used whereHas to filter data based on child model and in third we simply used join to filter data.

Let’s understand Filter Data Using Relational Model in Laravel with simple example

Step 1: Create a fresh laravel project

Open a terminal window and type below command to create a new project

composer create-project laravel/laravel blog

You can also read this to start with new project

Step 2 : Create a table and models

I assume the you have already installed the laravel and basic connection of it like database connection and composer.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel-jwt
DB_USERNAME=root
DB_PASSWORD=

Now, for an example i am creating here two table users and articles using migration and then i am going to fill the data in it, so create model and migration

php artisan make:model Article -m

this will generate the model and migration file

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Article extends Model
{
   
   use HasFactory;
    
}

and the default user model

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'name',
        'email',
        'password',
    ];

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array<int, string>
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * The attributes that should be cast.
     *
     * @var array<string, string>
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function articles(){
      $this->hasMany(Article::class);
    }
}

Here we create the relation between user and articles using hasMany relationship.

and migration file database/migrations/timestamp_create_articles_table.php

<?php

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

return class extends Migration // for laravel 9
// class CreateArticle extends Migration // for laravel below 9
{
    /**
     * Run the migrations.
     *
     * @return void
     */
     public function up()
    {
        Schema::create('articles', function (Blueprint $table) {
            $table->id();
            $table->string('email')->unique();
            $table->int('user_id');
            $table->string('title');
            $table->string('body')->nullable();
            $table->enum('status',['1','0']);
            $table->timestamps();
        });
    }

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

and then migrate the migration

php artisan migrate

Step 3 : Fetch Relational Record

Now, we have create the models and relations, now fetch the filtered records by creating the routes and fetch data using three methods

Get Relational data using has method

In this method we will filter data using has method which count the records in child table and return the filter parent model

<?php

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

Route::get('user-with-articles',function(){
 return  User::has("articles",">",0)->get();
});

This will return users who has posted at least 1 article.

Get Relational data using whereHas method

In this method we will filter data using whereHas method by which we can filter data by column name and where condition

<?php

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

Route::get('user-with-articles',function(){
 return  User::whereHas("articles",function($query){
            $query->where("status",'1');
         })->get();
});

This will return users who has posted at least 1 article.

Get Relational data using Inner Join method

In this method we will filter data using inner join method by which we can filter data by column name and where condition as well

<?php

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

 
Route::get('user-with-articles',function(){
 return  User::join("articles","articles.user_id", "users.id")->where("status","1")->count();
});

This will return users who has posted at least 1 article.