Get specific column of the foreign key table instead of pure foreign key

@Rebwar Thanks for your reply Rebwar! I have tried both options and achieved what I wanted but I wanted to know something about the performance optimization.

In option 1:

I think that we are fetching all of the data as I have mentioned in my question JobPost::with('user', 'service')->get(); and then just removing the extra data from the collection and setting user name and service right? So I think that in this option we have created 2 more steps instead of optimizing the db performance.

In option 2:

We are fetching all of the data from 3 tables and then just executing 2 more queries to fetch service_name and user_name

Option 3: Running the raw sql query like

DB::table('job_posts as jp')
        ->join('services as s', 's.id', '=', 'jp.service_id')
        ->join('users as u', 'u.id', '=', 'jp.user_id')
        ->select('jp.*', 's.name as service_name', 'u.full_name as user_name')
        ->get();

I wanna know how much performance difference it's gonna make and what would be the best approach.

Sorry I'm a newbie in Laravel. Any help will be very appreciated.

Thanks!

@uzairusman1425 The first option gets all the data from the database using with(['user:id,name', 'service:id,name']) and then uses map() to add user_name and service_name to each job. While this method works, it can slow down if there are a lot of job posts because the processing happens in PHP after the data is fetched.

The second option is better because it uses addSelect to fetch user_name and service_name directly from the database. This reduces the need for extra processing. If you don't need the full user and service data, you can remove the with(['user', 'service']) part, which will make it even faster.

The third option is the fastest. It uses a single SQL query with joins to get all the needed data. This takes full advantage of the database's ability to handle relationships efficiently.

I tested all three options with 10,000 records and got these results:

Option 1 - Execution Time: 187.63303756714 ms
Option 2 - Execution Time: 96.518993377686 ms
Option 3 - Execution Time: 10.445833206177 ms

and if you remove the with(['user', 'service']) part from Option 2, the execution time will decrease to:: 47.78790473938 ms

@Rebwar Thanks for your precious time bro! Can you provide me the code you wrote for benchmarking the code execution if possible. It will be appreciated?

@uzairusman1425 I used PHP's microtime() to measure the execution time. But there are other ways to do it, like using the Laravel Debugbar package, which shows more details about query performance and other information.

// Helper function
function measureExecutionTime($callback, $iterations = 10) {
    $totalTime = 0;

    for ($i = 0; $i < $iterations; $i++) {
        $start = microtime(true);
        $callback();
        $end = microtime(true);

        $totalTime += ($end - $start) * 1000; // Convert to milliseconds
    }

    return $totalTime / $iterations; // Average time
}

$firstOptionTime = measureExecutionTime(function () {
    JobPost::with(['user:id,name', 'service:id,name'])
        ->get()
        ->map(function ($job) {
            $job->user_name = $job->user->name;
            $job->service_name = $job->service->name;

            unset($job->user, $job->service);

            return $job;
        });
});

$queryExecutionTimes[] = "Option 1 - Average Execution Time: {$firstOptionTime} ms";

$secondOptionTime = measureExecutionTime(function () {
    JobPost::with(['user', 'service'])
        ->addSelect([
            'user_name' => User::select('name')
                ->whereColumn('users.id', 'job_posts.user_id')
                ->limit(1),
            'service_name' => Service::select('name')
                ->whereColumn('services.id', 'job_posts.service_id')
                ->limit(1)
        ])
        ->get();
});

$queryExecutionTimes[] = "Option 2 - Average Execution Time: {$secondOptionTime} ms";

$thirdOptionTime = measureExecutionTime(function () {
    DB::table('job_posts as jp')
        ->join('services as s', 's.id', '=', 'jp.service_id')
        ->join('users as u', 'u.id', '=', 'jp.user_id')
        ->select('jp.*', 's.name as service_name', 'u.name as user_name')
        ->get();
});

$queryExecutionTimes[] = "Option 3 - Average Execution Time: {$thirdOptionTime} ms";

dd($queryExecutionTimes);

@Rebwar Thanks for your help!!!

@Tray2 Thanks for your reply! As you have mentioned about the resource usage I thought about it and I have wrote some options in the Rebrew reply. Please have a look and guide me. Thanks!

@uzairusman1425 the most performant is probably your initial query, but unless its thousands of rows the difference will be miniscule.

Go with option 2 with the subselects and drop the with statement as that is two unnecessary queries