Laravel - How to import excel file file and save into a table with foreign keys

Question

I am using Laravel-5.8 for a web application. Also, I am using maatwebsite-3.1 to import excel file.

Model: HrEmployee

protected $table = 'hr_employees';

protected $fillable = [
          'employee_code',
          'user_id',
          'address',
          'company_id',
          'email',
          'line_manager_id',
          'employee_designation_id',
          'employee_job_title_id',
          'employee_status_id',
          'employee_type_id',
          'employement_type_id',
          'employment_date',
          'first_name',
          'last_name',
      'is_hod'
      ];

public function user()
{
   return $this->belongsTo('App\User');
}

public function parent()
{
   return $this->belongsTo('HrEmployee', 'line_manager_id');
}

public function children()
{
   return $this->hasMany('HrEmployee', 'ine_manager_id');
}

public function company()
{
   return $this->belongsTo('App\Models\Organization\OrgCompany','company_id');
}

public function designation()
{
    return $this->belongsTo('App\Models\Hr\HrDesignation','employee_designation_id');
}

public function jobtitle()
{
    return $this->belongsTo('App\Models\Hr\HrJobTitle','employee_job_title_id');
}

public function employeestatus()
{
    return $this->belongsTo('App\Models\Hr\HrEmployeeStatus','employee_status_id');
}

public function employeetype()
{
    return $this->belongsTo('App\Models\Hr\HrEmployeeType','employee_type_id');
}

public function employementtype()
{
    return $this->belongsTo('App\Models\Hr\HrEmployementType','employement_type_id');
}

public function department()
{
   return $this->belongsTo('App\Models\Organization\OrgDepartment','department_id');
}

I have already configured the Maatwebsite Excel Package

app/Imports/EmployeesImport.php

namespace App\Imports;

use App\User;
use App\HrEmployee;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class EmployeesImport implements ToModel, WithHeadingRow
{
  public function model(array $row)
  {
    return new HrEmployee([
        'employee_code'     => $row['employee_code'],
        'email'    => $row['email'], 
        'first_name'    => $row['first_name'],
        'last_name'    => $row['last_name'],
        'line_manager_id'    => $row['line_manager_id'],
        'employee_designation_id'    => $row['employee_designation_id'],
        'employee_job_title_id'    => $row['employee_job_title_id'],
    ]);
  }
}

Controller

class HrEmployeesController extends Controller
{
  public function importExportView()
  {
     return view('import');
  }

  public function import() 
  {
    Excel::import(new EmployeesImport,request()->file('file'));

    return back();
  }
}

How do I re-write my code to accommodate the foreign keys (line_manager_id,employee_designation_id,employee_job_title_id), so that it will map the keys to the name. For example, if designation_name is entered to the excel sheet it should map it to employee_designation_id.

Thank you

Muhammad Dyas Yaskur Muhammad Dyas Yaskur · Accepted Answer · 2020-01-04T23:12:29

Just find the foreign key ID in the EmployeesImport

$row['employee_designation_id'] = HrDesignation::where("name", "like", "%".$row['designation']."%");
$row['line_manager_id']         = HrEmployee::where("first_name", "like", "%".$row['line_manager']."%");
$row['employee_job_title_id']   = HrJobTitle::where("name", "like", "%".$row['job_title']."%");

or the full code

namespace App\Imports;

use App\User;
use App\HrEmployee;
use App\Hr\HrDesignation;
use App\Hr\HrJobTitle;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class EmployeesImport implements ToModel, WithHeadingRow
{
    public function model(array $row)
    {
        $row['employee_designation_id'] = HrDesignation::where("name", "like", "%".$row['designation']."%");
        $row['line_manager_id']         = HrEmployee::where("first_name", "like", "%".$row['line_manager']."%");
        $row['employee_job_title_id']   = HrJobTitle::where("name", "like", "%".$row['job_title']."%");

        return new HrEmployee([
            'employee_code'           => $row['employee_code'],
            'email'                   => $row['email'],
            'first_name'              => $row['first_name'],
            'last_name'               => $row['last_name'],
            'line_manager_id'         => $row['line_manager_id'],
            'employee_designation_id' => $row['employee_designation_id'],
            'employee_job_title_id'   => $row['employee_job_title_id'],
        ]);
    }
}

Laravel - How to import excel file file and save into a table with foreign keys

2 Answers