Balance Through Completeness: Scaling Candidate Profiles in Laravel

When we build the job portal in early stage everything is simple and straight forward on the rules . Candidate sign up , fill few detail , complete the profile or not , apply for job , recruiter receive the job application and review it .

But when the user start to grow , recruiter user give a feedback about that lot of candidate without complete profile also make their job advertisement application full , at that time we only display and allow sorting based on date application . Its hard to get the quality application ( which is logically , candidate that have complete profile should be always someone that really want the job compare to non complete profile this can be act as a first filter criteria to get a good candidate )

Easy fix

For the easy fix when we want to provide the ability to sort , filter by profile completeness , we just calculate on the fly for profile scoring completeness .

$jobApplications = JobApplication::select('job_applications.*')
    ->join('candidates', 'job_applications.candidate_id', '=', 'candidates.id')
    ->leftJoin('resumes', 'resumes.candidate_id', '=', 'candidates.id')
    ->leftJoin('experiences', 'experiences.candidate_id', '=', 'candidates.id')
    ->leftJoin('educations', 'educations.candidate_id', '=', 'candidates.id')
    ->selectRaw('
        (
            (CASE WHEN candidates.name IS NOT NULL THEN 1 ELSE 0 END) +
            (CASE WHEN candidates.email IS NOT NULL THEN 1 ELSE 0 END) +
            (CASE WHEN resumes.id IS NOT NULL THEN 1 ELSE 0 END) +
            (CASE WHEN experiences.id IS NOT NULL THEN 1 ELSE 0 END) +
            (CASE WHEN educations.id IS NOT NULL THEN 1 ELSE 0 END)
        ) / 5 * 100 as profile_completeness
    ')
    ->groupBy('job_applications.id', 'candidates.id')
    ->orderByDesc('profile_completeness')
    ->paginate(20);

This working perfectly with our current rate of candidate and the application , somehow when we reach a large amount of candidate and application , new calculation criteria which is not only for profile completeness , this query will be impacted and become slow and also really hard to maintain it since we will have many join and other calculations .

Searching For Better Solution

Since the current approach when the application is high , new metric to be calculate the performance will be impacted . It also hard to maintain whenever we need to change the calculation and criteria . The calculation also not reusable as previously we also display this calculation to each candidate also .

Instead of recalculation on the fly , we decide to store the completeness score in another table and kept updated automatically based on candidate data change .

First step we design the table that will store the completeness of the each candidates . This is example

Schema::create('profile_completions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('candidate_id')->constrained()->onDelete('cascade');
    $table->unsignedTinyInteger('profile_completeness')
          ->default(0)
          ->comment('Percentage of profile completeness, 0-100');
    $table->timestamps();
});

Of course for real one , we have another information in here :)

Once we have this , we need to ensure we can do calculation based on event that trigger by candidate .

We create a service for calculation . This is only basic example .

namespace App\Services;

use App\Models\Candidate;

class ProfileCompletenessCalculator
{
    protected Candidate $candidate;

    public function __construct(Candidate $candidate)
    {
        $this->candidate = $candidate;
    }

    public function calculate(): int
    {
        $fields = [
            'name' => !empty($this->candidate->name),
            'email' => !empty($this->candidate->email),
            'resume' => $this->candidate->resume()->exists(),
            'experience' => $this->candidate->experiences()->exists(),
            'education' => $this->candidate->educations()->exists(),
        ];

        $filledCount = collect($fields)->filter()->count();
        $total = count($fields);

        return intval(($filledCount / $total) * 100);
    }

    public function updateDatabase(): void
    {
        $this->candidate->profileCompletion()->updateOrCreate(
            ['candidate_id' => $this->candidate->id],
            ['profile_completeness' => $this->calculate()]
        );
    }
}

To keep it simple , as an example we can add this in model that are related , as example we put it in resume model .

protected static function booted()
{
    static::saved(function ($resume) {
        (new \App\Services\ProfileCompletenessCalculator($resume->candidate))->updateDatabase();
    });
    static::deleted(function ($resume) {
        (new \App\Services\ProfileCompletenessCalculator($resume->candidate))->updateDatabase();
    });
}

Done , now every time user update any of this , it will trigger calculation and update it into profile_completions table .
We can use observer also for this instead using booted here .

Result Of The Solution

Now, when recruiters browse applications we can just use this

$jobApplications = JobApplication::query()
    ->with(['candidate.profileCompletion'])
    ->join('profile_completions', 'profile_completions.candidate_id', '=', 'job_applications.candidate_id')
    ->orderByDesc('profile_completions.profile_completeness')
    ->paginate(20);

No more subqueries , no recalculations on every request and now the query will be more simple to like this :

# Get only candidates with > 70% complete profile
$jobApplications = JobApplication::query()
    ->with(['candidate.profileCompletion'])
    ->join('profile_completions', 'profile_completions.candidate_id', '=', 'job_applications.candidate_id')
    ->where('profile_completions.profile_completeness', '>', 70)
    ->orderByDesc('profile_completions.profile_completeness')
    ->paginate(20);

# See only fully complete profiles:
JobApplication::query()
    ->join('profile_completions', 'profile_completions.candidate_id', '=', 'job_applications.candidate_id')
    ->where('profile_completions.profile_completeness', 100)
    ->orderBy('job_applications.created_at', 'desc')
    ->get();

Closing

Both solution work , in early stage its okay to take shortcuts sometime , we dont need a perfect one when we only have few candidates and recruiters using the platform . The calculate on the fly is simple and quick for us to moving fast and release the feature to recruiters to use it .

But as the platform grew , we got the feedback . The thing that i learn sometime , dont prematurely optimize , but don't wait too long either . Start simple , but once it reach some stage , start to properly design the solution :)

Comments

[david duymelinck]

Why do the calculation in a query in the first place? Do the calculation when the profile is added or updated by checking the input, and add that number to the job_applications table.