Dfyjkt

I have custom table with users work experiences:

Schema::create('workplaces', function (Blueprint $table) 
 $table->increments('id');
 $table->unsignedInteger('user_id');
 $table->foreign('user_id')
 ->references('id')
 ->on('users')
 ->onDelete('cascade');
 $table->string('company')->nullable();
 $table->string('position')->nullable();
 $table->string('description')->nullable();
 $table->smallInteger('from')->nullable();
 $table->smallInteger('to')->nullable();
 $table->timestamps();
);

Here example user experiences data:

----------------------------------------------------------
| user_id | company | position | description | from | to |
----------------------------------------------------------
----------------------------------------------------------
| 1 | Google | Designer | Lorem ipsum | 2018 |null|
----------------------------------------------------------
----------------------------------------------------------
| 1 | Yahoo | Designer | Lorem ipsum | 2014 |2017|
----------------------------------------------------------
----------------------------------------------------------
| 1 |Microsoft| Designer | Lorem ipsum | 2004 |2008|
----------------------------------------------------------

In this example user with id == 1 has 7 years work experience.

2018 - (2017 - 2014) - (2008 - 2004) = 2011

User last year work in 2018 and now I need to subtraction result from last working year:

2018 - 2011 = 7

Now, current user has 7 year work experience.

How I can calculate custom work experiences using laravel eloquent?

1) Create a model in app folder where filename is Workplace.php with following content:

<?php namespace App;

use IlluminateDatabaseEloquentModel;

class Workplace extends Model 

 protected $table = 'workplaces';

 protected $fillable = ['user_id', 'company', 'position', 'description', 'from', 'to'];

 public $timestamps = true;


 public function user()
 
 return $this->belongsTo('AppUser');
 

 public function experienceYears() 
 
 $from = property_exists($this, 'from') ? $this->from : null;
 $to = property_exists($this, 'to') ? $this->to : null;
 if (is_null($from)) return 0;
 if (is_null($to)) $to = $from; // or = date('Y'); depending business logic
 return (int)$to - (int)$from;
 

 public static function calcExperienceYearsForUser(User $user) 
 
 $workplaces = 
 self::with('experienceYears')
 ->whereUserId($user->id)
 ->get(['from', 'to']);
 $years = 0;
 foreach ($workplaces AS $workplace) 
 $years+= $workplace->experienceYears;
 
 return $years;
 

2) Use it in controller's action:

$userId = 1;
$User = User::findOrFail($userId);
$yearsOfExperience = Workplace::calcExperienceYearsForUser($User);

I believe you should do such calculations on the DB side. It will be a much faster and more flexible solution. What if you got 1 million users with multiple experience entries and want to select top 10 most experienced users? Doing such calculation on the PHP side will be extremely inefficient.

Here's a raw query (Postgres) that might do the main part of the job:

SELECT SUM(COALESCE(to_year, extract(year from current_date)) - from_year) from experiences;

If you want to play around with it and test other cases: http://sqlfiddle.com/#!9/c9840b/3

Others might say that this is a premature optimization, but it's a one liner. Raw queries are very capable and should be employed more often.