[SOLVED] Laravel: Selecting all films where the condiction is in another table

Laravel: Selecting all films where the condiction is in another table

I have two models ("Films" and "Tags") related in a Many to Many relation. I have therefore Three Tables: "films", "tags" and "film_tag" EDITED: I have belongsToMany relations in place like this:

film.php

public function tags()
{
    return $this->belongsToMany('App\Tag', 'film_tag');
}

tag.php

public function films()
{
    return $this->belongsToMany('App\Film');
}

I want to get a list of all films that have a tag. I am passing the variable "$tag" which contaings the slug of the tag.

In my Database I have this

Table TAGS:

id  Tag

1   BEST

Table FILMS:

id      Film
1       Fantomas
2       Minions

Table FILM_TAG

id      Film_id     Tag_id
1       1           1
2       2           1

So, one tag asigned to two films.

1- Using Laravel Eloquent I did this:

    if (isset($tag)) {
        $all_element = Film::whereHas('tags', function ($query) {
                $query->where('tag', $tag);
            })
            ->get();

    }

But I get the following error message:

Undefined variable: tag

QUESTIONS:

how do I "join" the tow tables in Eloquent?
how could I make the variable recoffnized inside the query function?

2- Using normal laravel queries with DB I did this:

    if (isset($tag)) {
        $all_element = DB::table('films')
            ->join('film_tag', 'film_tag.film_id', '=', 'films.id')
            ->join('tags', 'tags.id', '=', 'film_tag.tag_id')
            ->select('films.*')
            ->where('tags.slug', '=', $tag)
            ->get();
    }

In this case I get just the first film repeated the number of times that this relation exist. In this case "Fantomas" two times. If I would have ten related films, I would get the first film repeated ten times.

In the other hand this variant do not give back a collection and therefore is difficult to manipulate the results (since all the methods are build to works with collections of objects)

3- I did a third solution which get the right solution: getting all films and then foreach

$all_element = Film::get();

if (isset($tag)) {
    $element_paused = array();
    foreach ($all_element as $element) {
        if (count($element->tags) > 0) {
            foreach ($element->tags as $gat) {
                if ($gat->slug === $tag) {
                    array_push($element_paused, $element);
                }
            }
        }
    }
    $all_element = $element_paused;
}

but this is a very SLOW solution, since I have more that 700 films in the Database

I would like to implement the first approach, which is very fast, but I get the repeated film all the time. Any help to find the bug would be appreciated

Solution

As you are using belongsToMany relationship this is the best approach to get all your films.

if (isset($tag)) {
    $all_element = Film::whereHas('tags', function ($query) use($tag) {
            $query->where('slug', $tag);
        })
        ->get();

}

Look you are missing the use($tag) portion for what you are getting undefined variable: tag error.