Laravel Eloquent Join Using Another table

Question

Hi how to convert this SQL query to Laravel Eloquent?
I have three table: payments, devices and users. Payments table does not store user_id. but table has device_id. And device table also has user_id.
I want to get user name which made payment.

select * from payments p 
join devices d on p.device_id = d.devid
join users u on d.user_id = u.id

Ali Sharifineyestani Ali Sharifineyestani · Accepted Answer · 2020-02-23T09:46:06

it's very easy

$users = \DB::table('payments')
    ->join('devices', 'devices.id', '=', 'payments.device_id')
    ->join('users', 'users.id', '=', 'devices.user_id')
    ->select('payments.*', 'users.name')
    ->get();

Another way is:

We have three models:

User (has many devices)
Device(belongs to user && has many payments)
Payment (belongs to device)

1) User.php

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use Notifiable;

    public function devices()
    {
        return $this->hasMany(Device::class);
    }

2) Device

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Device extends Model
{
    //
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function payments()
    {
        return $this->hasMany(Payment::class);
    }
}

3)Payment

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Payment extends Model
{
    //
    public function device()
    {
        return $this->belongsTo(Device::class);
    }
}

and your query should be:

Payament::with(['device','device.user'])->get();

Laravel Eloquent Join Using Another table

1 Answers