Laravel ORM for Join Multiple Table

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();

laravel.com/docs/5.4/eloquent-relationships has all the information you need — apokryfos
– apokryfos, Commented Mar 28, 2017 at 8:22
Nothing really. You're using ORM and I assume joins are a better fit here than relationships. You could replace ->where('employees.id', $id)->get() with ->find($id). Oh and you could make a model Employee instead of using \DB::table('employees'). — DevK
– DevK, Commented Mar 28, 2017 at 9:00

Gaurav Gangopadhyay · Accepted Answer · 2017-03-28 09:21:20Z

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'
        ]
    ],
    .....
]

Hamid Naghipour · Accepted Answer · 2017-03-28 08:56:40Z

1

you can use from this :

$row = $this->model->
where("id",$id)
->with("employee_finances")
->with("employee_addresses")
->with("employee_jobs")
->with("employee_admins")
->with("employee_personals")
->with("employee_memberships")
->with("employee_finances")
->get();

 return $row->isEmpty() ? [] : $row->toArray();

plz define relations in your models with these names and use form that, here.

answered Mar 28, 2017 at 8:56

Hamid Naghipour

3,6362 gold badges30 silver badges56 bronze badges

Collectives™ on Stack Overflow

Laravel ORM for Join Multiple Table

2 Answers 2

Step 1

Step 2

Step 3

Result

Original Result object

New result using Models

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Step 1

Step 2

Step 3

Result

Original Result object

New result using Models

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related