Question 300719 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Merging two eloquent queries into one result

I have two tables in my db.
Table Permit

ID Permit_type Max_allowed
1 Full 24
2 Half 12
3 Quarter 6

Table Entry

ID User_ID User_Name Permit_type Duration Status Date
1 1 John Smith Full 6 Approved 2023-12-08
2 2 Adam West Full 8 Approved 2023-12-08
3 1 John Smith Full 2 Pending 2023-12-08
4 1 John Smith Half 2 Approved 2023-12-09
5 1 John Smith Quarter 1 Approved 2023-12-07
6 2 Adam West Full 3 Approved 2023-12-08
7 1 John Smith Full 2 Approved 2023-12-08
8 1 Adam West Half 2 Approved 2023-12-09
9 1 John Smith Quarter 1 Approved 2023-12-07

I want to view the Entry details for each individual where the status is approved.

For : John Smith

Permit_Type Max_allowed Sum(Duration)
Full 24 8
Half 12 2
Quarter 6 2

For : Adam West

Permit_Type Max_allowed Sum(Duration)
Full 24 11
Half 12 2
Quarter 6 0

routes:

Route::get('/entry_details', [EntryController::class, 'get_entry_details']);

EntryController:

    public function get_entry_details(){

    $p1 = DB::table('Permit')->select('permit_type', 'max_allowed')->get();
    $p2 =DB::table('Entry')->where([['status', '=', 'Approved'],['user_id','=',1]])->select(DB::raw('SUM(duration) as duration'))->groupBy('permit_type')->get();

    $collection = collect($p1);
    $merged= $collection->merge($p2);
    $result = $merged->all();

    return view('/entry_details',$result);
    }

entry_details.blade.php

    <tr>
     <th>Permit Type</th>
     <th>Max Allowed</th>
     <th>Total Duration</th>
    </tr>
    @foreach($result as $data)
    <tr>
     <td>{{$data->permit_type}} </td>
     <td>{{$data-max_allowed}} </td>
     <td>{{$data->duration}} </td>
    </tr>
    @endforeach

Error:

Undefined variable $result
Error on line: **@foreach($result as $data)**

dd($result);
dd($result) looks like this

I don’t know what I did wrong

Tags:

2

Answers


  1. 0

    The problem is not in the query. You are not passing your variable in view correctly.

    To pass a variable to a view, you can use the following syntax:

    return view('/entry_details',[ 'result' => $result]);

    or

    return view('/entry_details',compact('result'));
    Login or Signup to reply.
  2. 0

    Why you are merging the two tables like this? Just join the two tables, will give you just one query and then you loop it over it in your blade.
    BTW you are passing the result variable into view not correctly.
    Change your code to this.

    public function get_entry_details()
{
    $result = DB::table('Permit')
        ->leftJoin('Entry', function ($join) {
            $join->on('Permit.permit_type', '=', 'Entry.permit_type')
                ->where('Entry.status', 'Approved')
                ->where('Entry.user_id', auth()->id());
        })
        ->select('Permit.permit_type', 'Permit.max_allowed', DB::raw('COALESCE(SUM(Entry.duration), 0) as duration'))
        ->groupBy('Permit.permit_type', 'Permit.max_allowed')
        ->get();



    return view('entry_details', compact('result'));
}

    And then in the blade entry_details loop over it like this

    <tr>
    <th>Permit Type</th>
    <th>Max Allowed</th>
    <th>Total Duration</th>
</tr>
@foreach($result as $data)
    <tr>
        <td>{{$data->permit_type}}</td>
        <td>{{$data->max_allowed}}</td>
        <td>{{$data->duration}}</td>
    </tr>
@endforeach
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search