Pivot table with more than two foreign ids

user_8309730 0
Viewed 39

So I have a table, cities

City table

Second table is movies,

enter image description here

And the third table is theatres

enter image description here

I have a Pivot table which connects all three of these.

enter image description here

What I am trying to achieve is, to get only those theatres where city_id and movie_id are a certain value. This is my controller

class MovieController extends Controller
{
    public function test(){
        $city_id=1;
        $movie_id=1;
        $th=Movie::where('id',$movie_id)->first();
        dd($th->theatre);
    }
}

I want all the theatres where movie_id=1 and city_id=1

Movie Model

class Movie extends Model
{
    use HasFactory;
    public function theatre(){
        return $this->belongsToMany(Theatre::class,'city_movie_theatre');
    }
    public function city(){
        return $this->belongsToMany(City::class,'city_movie_theatre');
    }
}

Theatre Model

class Theatre extends Model
{
    use HasFactory;
    public function city(){
        return $this->belongsToMany(City::class,'city_movie_theatre');
    }
    public function movie(){
        return $this->belongsToMany(Movie::class,'city_movie_theatre','theatre_id','movie_id');
    }
}

Pivot Table Model

use Illuminate\Database\Eloquent\Relations\Pivot;
class City_Movie_Theatre extends Pivot
{
    use HasFactory;
    protected $table='city_movie_theatre';
    
}
laravel-8 eloquent laravel php
2 Answers

In your threatre model, mention in city_id as pivot

class Theatre extends Model
{
    use HasFactory;
    
    ...

    public function movie(){
        return $this->belongsToMany(Movie::class,'city_movie_theatre','theatre_id','movie_id')->withPivot('city_id')->withTimestamps();
    }
}

Then put the where condition on your relation and on your pivot column

Theatre::whereHas('movie', function ($query) {
    return $query->where('movie_id', '=', 1);
})->wherePivot('city_id', '=', 1)->get();

If you want any dynamic value for movie_id then use use($movie_id) and put it in condition

$movie_id = $city_id = 1;
Theatre::whereHas('movie', function ($query) use($movie_id, $city_id){
    return $query->where('movie_id', '=', $movie_id);
})->wherePivot('city_id', '=', $city_id)->get();
user_94712830

You can use "relation" with a closure where you can define your matching condition.

Movie::with('theater', function($query) use ($theater_id, $city_id){
  $query->where('theater_id',$theater_id); 
  })->with('city', function($query) use ($city_id){
    $query->where('city_id',$city_id);})->get();
user_26843320
Related