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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With