Laravel ORM加入多个表

Question

I have 9 tables that will love to join together all have the foreign key employee_id from employee table. How can I get ORM distribution for it. Below is the DB function that join all the function.

$modelEmployee = \DB::table('employees')
                ->select('*')
                ->join('employee_finances', 'employees.id', '=', 'employee_finances.employee_id')
                ->join('employee_addresses', 'employees.id', '=', 'employee_addresses.employee_id')
                ->join('employee_jobs', 'employees.id', '=', 'employee_jobs.employee_id')
                ->join('employee_admins', 'employees.id', '=', 'employee_admins.employee_id')
                ->join('employee_personals', 'employees.id', '=', 'employee_personals.employee_id')
                ->join('employee_memberships', 'employees.id', '=', 'employee_memberships.employee_id')
                ->where('employees.id', $id)
                ->get();

Answer 1

Step 1

First, create model for you employee table and add various relations to other table models

Eloquent Model for employees table

namespace App\Models;

class Employee extends \Illuminate\Database\Eloquent\Model {

public function employee_finances()
{
    return $this->hasMany(\App\Models\EmployeeFinance);
}

public function employee_addresses()
{
    return $this->hasMany(\App\Models\EmployeeAddress);
}

public function employee_jobs()
{
    return $this->hasMany(\App\Models\EmployeeJob);
}

public function employee_admins()
{
    return $this->hasMany(\App\Models\EmployeeAdmin);
}

public function employee_personals()
{
    return $this->hasMany(\App\Models\EmployeePersonal);
}

public function employee_memberships()
{
    return $this->hasMany(\App\Models\EmployeeMembership);
}

}

Step 2

Now create models for other join tables. Below is an example of employee_finances table. (similarly, create other models)

namespace App\Models;
class EmployeeFinance extends \Illuminate\Database\Eloquent\Model {
  ...
}

Step 3

Then for your query, you can use relations using with and whereHas functions of query builder. This equivalent to the result of the query mentioned in the question but the structure of the outcome will be different;

\App\Models\Employee::with('employee_finances','employee_addresses','employee_jobs','employee_admins','employee_personals','employee_memberships')
->whereId($employeeid)
->whereHas('employee_finances')
->whereHas('employee_addresses')
->whereHas('employee_jobs')
->whereHas('employee_admins')
->whereHas('employee_personals')
->whereHas('employee_memberships')
->get();

Result

Original Result object

The original resultant will be an object of common builder object where you cannot fire further relation actions which can be defined in Model level. The original result will also be a flat array of the result and may have less. One example here would be the id column value would be replaced by the primary employee's table column id.

[
    0 => [
        'id' => 1,
        'employee_name' => 'Employee',
        'employee_finance_content' => 'finance_content',
        'employee_personal_content' => 'personal_content',
        'employee_jobs_content' => 'employee_jobs',
        'employee_addresses_content' => 'employee_addresses',
        'employee_admins_content' => 'employee_admins',
    ]
    ....
]

New result using Models

The result would be an instance of Employee model. The final result would be as an associative array where each relation would be an index of the array but the result will be an instance of the related Model, for example, the employee_finances would be an index or represent a column and the value contained within it will be an instance of EmployeeFinance on which you can further do ORM level activities.

[
    0 => [
        'id' => 1,
        'employee_name' => 'Employee'
        'employee_finances' => [
           'id' => 2,
           'employee_id' => 1,
           'employee_finance_content' => 'finance_content'
        ],
        'employee_addresses' => [
           'id' => 10,
           'employee_id' => 1,
           'employee_address_content' => 'employee_address'
        ]
    ],
    .....
]