CODEWITHSUNDEEP
phplaravellaravel-5.7
nanocv
nanocv

Reputation: 2229

Get Laravel models filtering by many to many relationship

I have this films database schema (only data related to the question):

persons (yes, I used "persons" instead of "people"...)
id
name

roles
id
name

roles_persons
person_id
role_id

And the corresponding models, defining a many to many relationship:

class Person extends Model
{
    protected $table = 'persons';

    public function roles(){
        return $this->belongsToMany('App\Role', 'roles_persons')->withTimestamps();
    }
}

class Role extends Model
{
    public function persons(){
        return $this->belongsToMany('App\Person', 'roles_persons')->withTimestamps();
    }
}

Everything fine so far.

One of the roles is "director".

Now I want to add a "films" table, which has a foreign key with the id of a director (a person with the "director" role).

films
id
title
director_person_id

In the create method of FilmsController, I'm trying to send to the view a list with the directors (in order to display a select input to choose one).

Which is the right way to do that?

class FilmsController extends Controller
{
    public function create()
    {
        $directorRole = Role::find('1');

        $directorsToChoose = Person::  ???

        return view('films.create')->with('directors', $directors);
    }
}

I saw this related question but I don't understand how to apply it in this case.

Thank you.

Upvotes: 0

Views: 1416

Answers (1)

Tharaka Dilshan
Tharaka Dilshan

Reputation: 4499

It's not a good approach to use Pivot Table keys as foreign keys on another table. In those kind of scenarios you may add a Primary key to the table and use that key as Foreign key.

person_roles
id (primary key)
role_id
person_id

films
id
person_role_id (foreign key)

Since this is not a Pivot Table, create an Eloquent Model too.

PersonRole

class PersonRole extends Model
{
    protected $table = 'persons_roles';

    public function role()
    {
        return $this->belongsTo(Role::class);
    }

    public function person()
    {
        return $this->belongsTo(Person::class);
    }
}

Person

class Person extends Model
{
    protected $table = 'persons';

    public function personRole
    {
        return $this->hasMany(PersonRole::class);
    }
}

Role

class Person extends Model
{
    protected $table = 'roles';

    public function personRole
    {
        return $this->hasMany(PersonRole::class);
    }
}

And then send those tables ids to the Films Create Form to select.

class FilmsController extends Controller
{
    public function create()
    {
        $directors = PersonRole::whereHas('role', function ($roles) {
            $roles->where('name', 'director');
        })
        ->with('person')
        ->get();

        return view('films.create')->with('directors', $directors);
    }
}

View

<select>
    @foreach($directors as $director)
        <option value="{{ $director->id }}">{{ $director->person->name }}</option>
    @endforeach
</select>

Upvotes: 2

Related Questions