filter results on pivot table
Hi I have to tables with many to many relationship
Invoice Model
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Invoice extends Model
{
public $primaryKey = 'id';
protected $fillable = [
'user_id',
'date_ins',
'tas',
];
/**
* The roles that belong to the invoice.
*/
public function shifts()
{
return $this->belongsToMany(Shift::class, 'invoice_shift')
->withPivot(['invoice_id','shift_id','shift_swapped_date','user_id','msg']);
}
}
Shift Model
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Shift extends Model
{
public $primaryKey = 'id';
protected $fillable = [
'code',
'description',
];
/**
* The users that belong to the role.
*/
public function invoices()
{
return $this->belongsToMany(Invoice::class, 'invoice_shift');
}
}
I have also User Model
<?php
namespace App;
use Illuminate\Notifications\Notifiable;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
class User extends Authenticatable
{
use Notifiable;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name', 'surname', 'code', 'email', 'password', 'is_admin'
];
/**
* The attributes that should be hidden for arrays.
*
* @var array
*/
protected $hidden = [
'password', 'remember_token',
];
/**
* The attributes that should be cast to native types.
*
* @var array
*/
protected $casts = [
'email_verified_at' => 'datetime',
];
}
i didn't define any relationship between User and the other two tables(Invoices, Shifts) In pivot invoice_shift I have a column in which i insert User id. I want to filter for example pivot table just for User_id = 1 but i need also 'user_id' info of the Invoice table that is the user that has created the invoice (and asked to substitute him to another user , info in pivot table). I tried so
$asked_shifts = Invoice::with(['shifts' => function ($query) use ($user_id) {
$query->wherePivot('user_id', '=', 1);
}])->get();
but it retrive always all rows of Invoices and only the row in the pivot with user_id = 1 How can i obtein only rows in Invoice table related also to user_id =1? Thx a lot
Please or to participate in this conversation.