CODEWITHSUNDEEP
phplaraveleloquent
Pattle
Pattle

Reputation: 6016

Laravel Eloquent: How to select from multiple tables

I'm using Laravel and the Eloquent ORM that it provides but I'm struggling to select the data I need. I have 3 Models

A house can have multiple occupants and I can easily get these using the following. 

$house= House::find($house_id);
$occupants = $house->occupants()->where('active', 1)->get();

This works nicely but I also want to select the job of each occupant. I've got this as a one to one relationship but the jobs are in a seperate table.

Is there a way to also select the related job for each occupant from the jobs table efficiently? I'm guessing it would be something like this

$occupants_and_jobs = $house->occupants()->where('active', 1)->job()->get();

Upvotes: 0

Views: 23861

Answers (3)

Maizied Hasan Majumder
Maizied Hasan Majumder

Reputation: 1053

Complete way to to search data from different tables in laravel:

Route:

Route::get('/systems/search', 'SearchController@search')->name('project-search');

Try this way in your searchController:

 public function search(Request $request)
    {
        $data = DB::table('projects as p')->select('p.*','u.first_name', 'u.last_name')
        ->join('users as u','p.user_id','=','u.id');
        if( $request->input('search')){
            $data = $data->where('p.title', 'LIKE', "%" . $request->search . "%")
            ->orWhere('p.description', 'LIKE', "%" . $request->search . "%")
            ->orWhere('u.first_name', 'LIKE', "%" . $request->search . "%")
            ->orderBy('p.created_at', 'desc');
        }
        $data = $data->paginate(16);
        return view('systems.search', compact('data'));
      
    }

And receive those data in your blade like this way:

<div class="col-md-8">
<h3>List of Results</h3>
<table class="table table-striped">
<tr>
<th>title</th>
<th>description</th>
<th>creator</th>
<th>category</th>
</tr>

@foreach($data as $search)
<tr>
<td>{{ $search->title }}</td>
<td>{{ $search->description }}</td>
<td>{{ $search->first_name }}</td>
</tr>
@endforeach
</table>
{{ $data->appends(request()->except('page'))->links() }}
</div>

search box input field:

<div class="input-group input_search" style="width: 100%">
                                    <input style="border-radius: 20px" type="text" class="form-control search"
                                        placeholder="Search" aria-describedby="basic-addon2" name="search" id="search"
                                        value="{{Request::get('title')}}">

Upvotes: 0

apokryfos
apokryfos

Reputation: 40653

You could just try what you are suggesting and see what happens. You can also do some eager loading of the relationships 

$house = House::with(["occupants","occupants.job"])->find($house_id);
foreach ($house->occupants as $occupant) {
     print_r($occupant->job);
}

As @AlexeyMezenin if you need to constrain the relationship then you need to (as the docs suggest under Constraining Eager Loads) do:

$house = House::with(["occupants" => function ($query) { 
    $query->where("active","=",1); 
},"occupants.job"])->find($house_id);
foreach ($house->occupants as $occupant) {
    print_r($occupant->job);
}

Now the fine-print: Laravel will include "with" relationships in the order it finds them and also include all intermediate relationships of the nesting, e.g. ::with("occupants.job") implies ::with(["occupants","occupants.job"]) however if you already have set a previous relationship then it is maintained (which is how this works). occupants will not be overwritten when occupants.job is set.

Upvotes: 3

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

This query will load all occupants with active = 1 and their jobs:

House::with(['occupants' => function($q) {
    $q->where('active', 1);
}, 'occupants.job'])->find($house_id);

Upvotes: 1

Related Questions