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 blogYou 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 -mthis 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 migrateStep 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.