CODEWITHSUNDEEP
laravelmodellaravel-5query-builder
Jonathan Solorzano
Jonathan Solorzano

Reputation: 7032

How to get data from two related tables in laravel 5?

I've got two models Employees and Departments, i would like to get the all the data from the employee including it's 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;

The result of that is:

------------------------------------------
| Firstname | Surname | Age | Department |
------------------------------------------
| John      | Doe     | 25  | Finance    |

How could i get the same result in Laravel 5?

I know that i can get all the data of a model like this:

$data = Employees::all();

But i need the Department column as the value of the parent table.

Using the Query Builder it look like this

 DB::table('Employees')
            ->join('Departments', 'Employees.Department', '=', 'Departments.ID')
            ->select('Employees.Firstname', 'Employees.Surname', 'Employees.Age', 'Departments.Name')
            ->get();

And using Eloquent it look 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 the data through @include and access to 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 the fields from the parent like Departments.Name in this case, i don't know if i'm missing something when calling it in the view. Also i would like to know how could i use aliases for the table columns.

Upvotes: 3

Views: 25639

Answers (1)

Khan Shahrukh
Khan Shahrukh

Reputation: 6411

In your model Employee create a function department 

public function department()
  {
      return $this->hasMany('App\Department');
  }

Then in your controller do like this 

$results = Employee::with('department')->get();

This is how you will get all the departments of the employee, but make sure both the table are related on a foreign key basis

Upvotes: 7

Related Questions