Ridiculous Ridiculous - 21 days ago 8
MySQL Question

Query where column is in another table - laravel

I have 3 tables called categories, seasons and movies.

categories table


+----+---------+------------+-------------+
| id | name | slug | created_at |
+----+---------+------------+-------------+
| 1 | It | it-2017 | SOME TIME |
| 2 | Another | another | SOME TIME |
| 3 |SpiderMan| spider-man | SOME TIME |
| 4 | Cars 3 | cars-3 | SOME TIME |
+----+---------+------------+-------------+


seasons table


+----+---------+------------+-------------+
| id | number |category_id | created_at |
+----+---------+------------+-------------+
| 1 | 1 | 2 | SOME TIME |
| 2 | 2 | 2 | SOME TIME |
| 3 | 1 | 3 | SOME TIME |
| 4 | 3 | 1 | SOME TIME |
+----+---------+------------+-------------+


movies table


+----+---------+------------+-------------+
| id | name | slug | season_id |
+----+---------+------------+-------------+
| 1 | Pilot | pilot | 1 |
| 2 | Second | second | 1 |
| 3 | Third | third | 1 |
| 4 | Fourth | fourth | 1 |
+----+---------+------------+-------------+


And I made that URL is going to something like this

mywebpage.com/serie/{category_slug}/season-{season_number}

But where I go to this URL, query is going to show all movies in all seasons.
Not the season from current category.

My Controller

public function getMovies($category_slug, $season_number)
{
$categories = Category::where('slug', $category_slug)->get();
$seasons = Season::with('movies')->where('number', $season_number)->get();

return view('routes.getMovies', compact('categories', 'seasons'));
}


And my view getMovies.blade.php

@foreach($categories as $category)
@foreach ($seasons as $season)
@foreach ($season->movies as $movie)
Season - {{ $season->number }}
{{ $movie->name }}
{{ $movie->description }}
@endforeach
@endforeach
@endforeach


And my output is like Season 1 from all categories, but I want only to show seasons from current Category.

Answer Source

First, make sure have set the right relation on your models

Movie Model

public function season()
{
    return $this->belongsTo(Season::class, 'season_id');
}

Season Model

public function category()
{
    return $this->belongsTo(Category::class, 'category_id');
}

Then, the following will get all movies with a specific season number and specific category slug

$movies = Movie::whereHas('season', function ($season) use ($season_number) {
    $season->where('number', $season_number);
})
->whereHas('season.category', function ($category) use ($category_slug) {
    $category->where('slug', $category_slug);
})
->with('season')
->get();

and in view

@foreach ($movies as $movie)
    Season - {{ $movie->season->number }}
    {{ $movie->name }}
    {{ $movie->description }}
@endforeach