Laravel relationship between 3 tables where 2 are connected via pivot-CodePudding

I am new to Laravel and looked already for a similiar thread but I didnt find something. I want to use Eloquent and I got 3 Models and Tables: Testseries, Devices and Users.

The Users has a many to many relation to devices. (One User has many devices and vica versa) And Devices has a one to many relation to testseries. (One Device has many testseries and many testeries has one device)

** Table structure Users:**

id
username

Table structure Devices:

id
serial_number <-- its a string, not important for the structure

Table structure Testseries:

id
device_id

Devices and Users are connected via Pivot

device_user:

id
user_id
device_id

If a User is logged in, I want to show all Testseries from all Devices that are connected to the User.

I defined in the User Model:

public function devices(): \Illuminate\Database\Eloquent\Relations\BelongsToMany {
    return $this->belongsToMany(Device::class);
}

And in the Device Model:

public function users(): \Illuminate\Database\Eloquent\Relations\BelongsToMany {
    return $this->belongsToMany(User::class);
}

public function testseries(): \Illuminate\Database\Eloquent\Relations\HasMany {
    return $this->hasMany(Testserie::class);
}

Is there any way to create function inside the User Model which can easily access to the testserie?

If someone doesnt understand what I want because my English isnt good. This function should tell what I want inside the User Model:

public function testseries() {
    return $this->devices()->testseries();
}

Also I want all testseries at one query.

I tried with the each method. But its doing for each device a single query to the testserie.

I also tried it with the with method. It works, but I want all Columns from the Testseries Table, but then I have to tell all table names inside the array and I dont want the columns from the Devices table.

I expected to get a query when I call the ->get Method that I'll get all Testseries at once with a single query.

CodePudding user response：

Try this :

Auth::user()->with('devices.testseries')->get();

CodePudding user response：

I found a way,

I added this function to my User Model:

 public function testseries(): \Illuminate\Support\Collection {
    return Testserie::whereHas('device', function(Builder $query){
        $query->whereHas('users', function(Builder $query){
            $query->where ('user_id', '=', \Auth::user()->getKey());

        });
    })->get();
}

This works. I dont know if its the cleanest way. If someone has a better way. Dont be shy to share your thoughts.