• 6
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

How to select columns from joined tables: laravel eloquent

I have a different problem from this. The scenario is same but I am in need of more filtration of the results.

Let me explain.

Consider I have 2 tables

vehicles

 id
 name
 staff_id
 distance
 mileage

staffs

 id
 name
 designation

I want to select only id and name from both tables(Models). The Vehicle Model contain a belongsTo relation to Staff model.

class Vehicle extends Model
{
    public function staff()
    {
      return $this->belongsTo('App\Staff','staff_id');
    }
}

and I joined using this

Vehicle::where('id',1)
            ->with(['staff'=> function($query){
                            // selecting fields from staff table
                            $query->select(['staff.id','staff.name']);
                          }])
            ->get();

When I put fields in ->get() like this

->get(['id','name'])

it filters the vehicle table but produce no result of Staff table.

Any ideas?

Finally found it.. In the ->get() you have to put the 'staff_id' like this

Vehicle::where('id',1)
            ->with(['staff'=> function($query){
                            // selecting fields from staff table
                            $query->select(['staff.id','staff.name']);
                          }])
            ->get(['id','name','staff_id']);

Since I didn't take the staff_id, it couldn't perform the join and hence staff table fields were not shown.

  • 8
Reply Report

You can use normal join like this:

Vehicle::join('staff','vehicles.staff_id','staff.id')
         ->select(
                  'vehicles.id',
                  'vehicles.name',
                  'staff.id as staff_id',
                  'staff.name'
          )
         ->get();

Since, you can't take both id at once in a join because only one is allowed. So, you can staff's id as staff_id.

You can add vehicle id condition with where clause like:

Vehicle::join('staff','vehicles.staff_id','staff.id')
         ->where('vehicle.id',1)
         ->select(
                  'vehicles.id',
                  'vehicles.name',
                  'staff.id as staff_id',
                  'staff.name'
          )
         ->get();

Hope you understand.

  • 6
Reply Report

The shortest and more convenient way I guess would be :

Vehicle::select('id','name','staff_id')->where('id',1)
->with('staff:id,name' )->get();

foreign key should present for selection .

Since Laravel 5.7 you can use with() like this :

with('staff:id,name' )

for granular selection.

  • 2
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions