How to get data from two related tables in laravel 5?
I have two models Employees and Departments, I would like to receive all data from an employee including his department, something like this:
SELECT e.Firstname, e.Surname, e.Age, d.Name as Department FROM Employees as e INNER JOIN Departments as d ON e.Department = d.ID;
Result:
------------------------------------------
| Firstname | Surname | Age | Department |
------------------------------------------
| John | Doe | 25 | Finance |
How can I get the same result in Laravel 5?
I know I can get all model data like this:
$data = Employees::all();
But I need the Department column as the value of the parent table.
Using Query Builder looks like this:
DB::table('Employees')
->join('Departments', 'Employees.Department', '=', 'Departments.ID')
->select('Employees.Firstname', 'Employees.Surname', 'Employees.Age', 'Departments.Name')
->get();
And using Eloquent looks like this:
Model
public function department()
{
return $this->belongsTo('App\Departments','Department','ID');
}
controller
$data->employees = Employees::with('department')->get();
Using the query builder, I can pass data through @include
and access it like this:
app.blade.php
@include('Club.index.employees',['employees'=>$data->employees])
employees.blade.php
@foreach($employees as $employee)
@include('Club.index.member',['employee'=>$employee])
@endforeach
member.blade.php
<h3>{{ $employee->Firstname }}</h3>
<p class="member-surname">{{ $employee->Surname }}</p>
...
And it works, but when I use Eloquent it doesn't display fields from the parent type, for example Departments.Name
, in this case, I don't know if I'm missing something when I call it in the view. Also I would like to know how I can use aliases for table columns.
source to share
In your Employee model, create a functional department
public function department()
{
return $this->hasMany('App\Department');
}
Then in your controller do the following:
$results = Employee::with('department')->get();
This is how you get all departments of an employee, but make sure both tables are foreign key related
source to share