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.

[KILLALLSKYWALKER]

Yes, you are correct. In the early days I also made that mistake 😅. I just calculate inside the query because it was fast and simple when we had only few users. But later, when more criteria and more users came, the query became very slow and hard to manage.

We cannot put the score directly into job_applications, because there are too many profile criteria. If we put all of them, the table will be too big with many unrelated columns and score is not really part of the job application itself it is only for comparing candidates with each other also . @xwero

[david duymelinck]

If we put all of them, the table will be too big with many unrelated columns and score is not really part of the job application itself

I agree you don't add all criteria to the table, but that is not needed to add the score to table.

I do think it is a part of the job application because it is the sum of its parts. Putting it in its own table is not wrong, but I think the cohesion is strong enough to be a column in the principal table.