In programming, as the size of the project grows, the complexity of the application also grows automatically. In this article, we will learn how we can get the count records that are linked with another table using hasMany relation. We will discuss how we can achieve result using different approaches and their benefits and drawbacks.
Let’s talk about the scenario that we are going through at the moment. Suppose, we have two tables called careers and applicants. The careers the table will store all the jobs opening. On the other hand, the applicants table will store all the job applications that are applied to each job. So, let’s create two tables using the below migration command.
//first command
php artisan make:migration create_careers_table
// second command
php artisan make:migration create_applicants_table
We need to structure the tables. Let’s first add some columns to the careers table. So, our final careers table migration file looks like below.
<?php
//careers migration file
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCareersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('careers', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->string('slug');
$table->text('content');
$table->string('status');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('careers');
}
}
Similarly, we also need to add required columns to the applicants table as well. We will add one foreign key to applicants which will point to the careers table. So, our final migration file looks like below.
<?php
//applicants migration file
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateApplicantsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('applicants', function (Blueprint $table) {
$table->id();
$table->foreignId('career_id')->constrained('careers');
$table->string('first_name');
$table->string('last_name');
$table->string('email');
$table->string('phone')->nullable();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('applicants');
}
}
Also, we need to create models for each table. Simply run the command below.
//model for careers table
php artisan make:model Career
//model for applicants table
php artisan make:model Applicant
After creating the model for each, we will add relations from careers table to applicants table. Please note that one job opening (i.e. one record in the careers table) can have many applicants. So, we will add hasMany relations between these two tables. Our Career model file looks as below.
<?php
//Career.php model
namespace Admin\Career\Models;
use Illuminate\Database\Eloquent\Model;
use Admin\Career\Database\factories\CareerFactory;
use Illuminate\Database\Eloquent\Factories\HasFactory;
class Career extends Model
{
use HasFactory;
protected $fillable = [
'title',
'slug',
'content',
'status',
];
public function applicants()
{
return $this->hasMany(Applicant::class);
}
protected static function newFactory()
{
return CareerFactory::new();
}
}
Similarly, we can also inverse the relation in Applicant model although it is optional.
Let’s seed our tables with dummy data. For this, we need to create factories and run the factory. Our definition of the factories looks like below.
<?php
//careers table factory definition
........
public function definition()
{
return [
'title' => $this->faker->jobTitle(),
'slug' => Str::slug($this->faker->jobTitle() ),
'content' => $this->faker->realText($maxNbChars = 200, $indexSize = 2),
'status' => $this->faker->randomElement(["Active", "Fulfilled", "Cancelled"]),
];
}
.......
//applicants table factory defintion
........
public function definition()
{
$random_career_id = Career::inRandomOrder()->pluck('id')->all();
return [
'career_id' => $this->faker->randomElement($random_career_id),
'first_name' => $this->faker->firstName($gender = 'male'|'female'),
'last_name' => $this->faker->lastName(),
'email' => $this->faker->email(),
'phone' => $this->faker->phoneNumber(),
];
}
.........
Now, here comes the question of fetching the careers with the total number of applicants. If we need to count the total number of applicants we can achieve this using eager loading and counting. But, this is not an efficient method as this results in N+1 redundancy problem. But, Laravel has a built in method named withCount(). This helps to count the record of the related table. So, our code looks like below.
<?php
//NOT Effecient Method
$careers = Career::with('applicants')->get();
foreach($careers as $career) {
$career->applicants = count($career->applicants);
}
// Efficient Method
// fetch all careers with total number of applicats
$careers = Career::withCount('applicants')->orderBy('updated_at', 'desc)->get();
We can also achieve this using the join. We need to join the careers table with the applicants table while accessing the table and attach the result in a new property in the careers object. However, we need to add a few lines of codes in the Career model.
<?
// Career model
public function applicants()
{
return $this->hasMany(Applicant::class);
}
public function applicantsCount()
{
return $this->hasOne(Applicant::class)
->selectRaw('career_id, count(*) as aggregate')
->groupBy('career_id');
}
public function getApplicantsCountAttribute()
{
if ( ! array_key_exists('applicantsCount', $this->relations))
$this->load('applicantsCount');
$related = $this->getRelation('applicantsCount');
// then return the count directly
return ($related) ? (int) $related->aggregate : 0;
}
Now, whenever we access the career model object, we can access the applicants using the applicantsCount property.
<?php
// lazy loading
$careers = Career::all();
$posts[0]->applicantsCount;
// eager loading
$careers = Career::with('applicantsCount')->get();
$careers->first()->applicantsCount;
This is all about accessing related model records. What are your thoughts? Don’t forget to drop your comments below.
