I am trying to convert the below DB query to an eloquent relation.

$device_barcodes = ["BSC0337", "BSC0503", "BSC0626"];
$devices = DB::table('devices')
       ->leftjoin('devices_repairs', 'devices_repairs.device_id', '=', 'devices.device_id')
       ->leftJoin('staff','staff.staff_id','devices_repairs.repair_damaged_by')
       ->whereIn('barcode', $device_barcodes)
       ->get();

So far I have the below, but I am struggling with the staff relation.

$devices = Devices::with('repairDetails')->whereIn('barcode', $device_barcodes)->get();

I've overcome this for now with the below, but this is not ideal.

foreach($devices as $device) {
           $staff_name = Staff::find($device->repairDetails->repair_damaged_by);
           $device->staff_name = $staff_name->staff_name;
 }

I am hoping for something like:

$devices = Devices::with('repairDetails')->with('staffDetails')->whereIn('barcode', $device_barcodes)->get();

This way I can show the device details, the repair details and the staff name on my blade form.

So the basic issue is I am trying to relate three models together.

Devices is the top model, where I join with DevicesRepairs using the device_id primary and foreign keys. But for the Staff model I need to join devices_repairs.repair_damaged by to staff.staff_id.

Here are my models: Devices:

class Devices extends Model
{
    /**
     * @var bool $timestamps Disable timestamps
     */
    public $timestamps = false;
    /**
     * @var string $table Name of the db table
     */
    protected $table = 'devices';
    /**
     * @var string $primaryKey Name of the primary key
     */
    protected $primaryKey = 'device_id';
    /**
     * @var array $fillable The attributes that are mass assignable.
     */
    protected $fillable = [
        'status',
        'order_reference',
        'model',
        'serial',
        'imei',
        'barcode',
        'mobile_number',
        'helpdesk_url_id',
        'device_notes'
    ];

    use Searchable;

    function repairDetails() {
        return $this->hasOne(DevicesRepairs::class, 'device_id');
    }

    function repairHistoryDetails() {
        return $this->hasOne(DevicesRepairsHistory::class, 'device_id');
    }

}

DevicesRepairs:

class DevicesRepairs extends Model
{
    use HasFactory;
    protected $table = 'devices_repairs';
    protected $primaryKey = 'device_id';
    public $timestamps = false;
    protected $fillable = [
        'device_id',
        'repair_damanged_by',
        'repair_damage_type',
        'repair_date_received',
        'repair_date_sent_for',
        'repair_damage_notes',
        'repairer_name',
        'repair_is_user_damage',
        'job_number',
        'operator_date_received',
        'operator_date_received_by',
        'operator_date_sent',
        'operator_sent_by',
        'photo_id',
        'photo_id_back'
    ];

    function device() {
        return $this->hasOne(Devices::class, 'device_id');
    }

    //This doesn't work - seems to return a random staff member.
    function staffDetails() {
        return $this->hasOne(Staff::class,'staff_id','repair_damaged_by');
    }  
}

Staff:

class Staff extends Model
{
    use searchable;

    /**
     * @var bool $timestamps Disable timestamps
     */
    public $timestamps = false;
    /**
     * @var string $table Name of the db table
     */
    protected $table = 'staff';
    /**
     * @var string $primaryKey Name of the primary key
     */
    protected $primaryKey = 'staff_id';
    /**
     * @var array $fillable The attributes that are mass assignable.
     */

    protected $fillable = [
        'staff_id',
        'staff_name',
        'manager_id',
        'is_active',
        'is_mdm_user',
        'user_id',
    ];
}

My ultimate aim is to be able to return fields like this in my view:

{{$device->barcode}}
{{$device->repairDetails->repair_damage_notes}}

//The above work fine but not this:
{{$device->staffDetails->staff_name}}

CodePudding user response：

treat at as a pivot table, a many to many relation between devices and staff

class Devices extends Model
{
    public function damagedBy()
    {
        return $this->belongsToMany(Staff::class, 'devices_repairs', 'device_id', 'repair_damaged_by');
    }

So the query will be like

$device_barcodes = ["BSC0337", "BSC0503", "BSC0626"];
$devices = Devices::with('damagedBy')->whereIn('barcode', $device_barcodes)->get();