Query through tables

Hey romain, try this out

class Actor extends Model
{
	public function shows()
	{
		return $this->hasManyThrough(
			'App\Models\Show'
			'App\Models\ShowDetails'
			'actor_id', // Foreign key on show_details table...
			'show_id', // Foreign key on show table...
			'id', // Local key on actor table...
			'id' // Local key on show_details table...
		);
	}
}

Docs

https://laravel.com/docs/8.x/eloquent-relationships#has-many-through

Easiest thing that comes to my mind:

$actor = Actor::find($actorId);

$showDetails = $actor->showDetails()->with(['show'])->paginate();

Then in your blade view you can use something like this:

@foreach($showDetails as $showDetail)
<p>{{ $showDetails->show->name }}</p>
@endforeach

{{ $showDetails->links() }}

But you can also add a relation to your Actor model to use a HasManyThrough relation, with some tweaked parameters:

class Actor extends Model {
    public function showDetails()
    {
        return $this->hasMany(ShowDetails::class);
    }

    public function shows()
    {
        $show = new Show();

        return $this->hasManyThrough(
            Show::class,
            ShowDetails::class,
            $this->getForeignKey(), // actor_id
            $show->getKeyName(),    // id
            $this->getKeyName(),    // id
            $show->getForeignKey(), // show_id
        );
    }
}

$shows = $actor->shows()->paginate();

Reference: https://laravel.com/docs/8.x/eloquent-relationships#has-many-through

neither seem to work. I get:

Column not found: 1054 Unknown column 'show_details.actor_id' in 'where clause' (SQL: select count(*) as aggregate from `shows` inner join `show_details` on `show_details`.`show_id` = `shows`.`id` where `show_details`.`actor_id` = 2)

I think that's because it doesn't know to look for the relationship in the table actor_show_details where I have actor_id and show_details_id

Have you made sure whether the ids are correct? We have just assumed that u would have these ids.

the ids are correct. I've used the default Laravel setup for all models. Nothing custom.

@romain can you explain again the relationship between these three tables - actor, show, showdetails ? Its better if you share the migrations of these tables

is it like this?

show has many show details. A show detail row has an actor id. thus an actor has many show detail entries.

It's like this: Show -> HasOne -> ShowDetails <-> BelongsToMany <-> Actor

To link ShowDetails and Actor I have a table: actor_show_details which stores actor_id and show_details_id

There you go :)

Find show_details for actor through actor_show_details. After this you can find the shows.

    class Actor extends Model
{
    public function showDetails()
    {
        return $this->hasManyThrough(
            'App\Models\ShowDetail',
            'App\Models\ActorShowDetails',
            'actor_id', // Foreign key on actor_show_details table...
            'show_detail_id', // Foreign key on show_details table...
            'id', // Local key on actor table...
            'id' // Local key on actor_show_details table...
        );
    }
}

You can't use Has Many Through relationship with Many to Many.

$actor = Actor::find($actorId);

$shows = Show::whereHas('showDetails.actors', function ($query) use ($actor) {
    $query->where('id', $actor->id);
})->paginate(15);

Docs: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

If I was you I would change naming

Pivot table has to be singular so instead of actor_show_details it should be actor_show_detail

And relationship in Show model is better to have just detail because it's hasOne

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Show extends Model
{
    public function detail()
    {
        return $this->hasOne('App\Models\ShowDetail');
    }
}

But in my opinion is better to have many to many relationship between Show and Actor models.

@romain I missed there were a pivot table between ShowDetails and Actor models.

So in the database level you have four tables, right?

show  ---  show_details   --{  actor_show_details  }--  actors

The ->hasManyThrough is handy, but basically what it does is adding a join to one intermediary model's table.

But here we need more joins. One less talked about Eloquent feature is that you can customize the query builder been used as the relation source.

Try adding this relation to your Actor model:

class Actor extends Model
{
    // ... other code

    public function showDetails()
    {
        return $this->belongsToMany(ShowDetails::class);
    }

    public function shows()
    {
        $query = Show::query()
            ->selectRaw('shows.*, actor_show_details.actor_id')
            ->join('show_details', 'show_details.show_id', 'shows.id')
            ->join('actor_show_details', 'actor_show_details.show_details_id', 'show_details.id');

        return $this->newHasMany($query, $this, 'actor_show_details.actor_id', 'id');
    }
}

You might need to change some columns names if needed. I tried guessing based on the conventions from how you described your models/tables.

Then you'll just need to:

$shows = $actor->shows;

Hope it helps.

ok so after some fiddling around, I landed with @michaloravec 's solution, with a small column adaptation:

class Actor extends Model
{
    public function shows()
    {
        return Show::whereHas('details.actors', function ($query) {
            $query->where('actor_id', $this->id); //<< Needed to specify `actor_id` of an ambiguous column name error is thrown
        });
    }
}

Now it works as expected, returning the Shows where a specific actor is.

Thanks to all who helped!