zipppy zipppy - 4 months ago 27
SQL Question

Laravel 5.2 three-way pivot with custom pivot model

I have the following problem:

I have three tables:

contacts (people)
departments
contact_types (e.g. IT-Contact)


All of them are many-to-many types; One person can be a responsible for 0-n departments as 0-n Contact_types (even for the same department as multiple types). And so on.

In addition i have to have a history all over the project, so each of the tables stores "valid_start" and "valid_end" timestamps as well.

Therefore i now have this relation table:

contact_contact_type_department
id
contact_id
contact_type_id
department_id
valid_start
valid_end


What i ended up doing is creating a Model for the intermediate table:

class DepartmentResponsible extends Model {

protected $table = 'contact_contact_type_department';

protected $fillable = [...];

protected $dates = [
'valid_start',
'valid_end',
];

protected static function boot(){
parent::boot();
static::addGlobalScope(new ValidScope());
}

public function contact() {
return $this->belongsTo('cap\Contact');
}

public function department() {
return $this->belongsTo('cap\Department');
}

public function type() {
return $this->belongsTo('cap\ContactType');
}
}


Contact Model:

class Contact extends CustomModel{
protected $dates = [...];

protected $fillable = [...];

protected static function boot(){
parent::boot();
static::addGlobalScope(new ValidScope());
}

public function departmentResponsibles() {
return $this->hasMany('cap\DepartmentResponsible');
}
}


ContactType Model:

class ContactType extends CustomModel {
protected $dates = [...];

protected $fillable = [...];

protected static function boot() {
parent::boot();
static::addGlobalScope(new ValidScope());
}

public function responsible() {
return $this->hasMany('cap\DepartmentResponsible');
}
}


Department Model:

class Department extends CustomModel {
protected $fillable = [...];

protected $dates = [...];

protected static function boot(){
parent::boot();
static::addGlobalScope(new ValidScope());
}

public function responsibles(){
return $this->hasMany('cap\DepartmentResponsible');
}

//other methods down here, which have no immpact on this issue
}


I can now do things like

Department::first()->responsibles


Regarding the issue with the timestamps on the pivot table i assume i will have to make it a custom pivot table again (already had to do that once, in another case, where i had a "regular" 2-way pivot table)

So my 2 Questions now are:

1. Is this even the right way to do it? I mean the whole thing with the intermediate model and so on. I tried other ways as well, but I couldn't get anything like
department->attach(contact)
to work since i always need the third id as well...

2. How can i get something like Department::first()->contacts to work? (In a way, where i can access the intermediate "responsibles (=contact_contact_type_department)" table and filter based on the validity dates;eg. with a scope or with wherepivot functions)

Answer

well i finally went with the approach that i have an intermediate model called responsible. So for example if i want to print all the contacts and their contact_types for a department then i can do something like this:

$department = Department::first();

<ul>
    foreach($department->responsible as $responsible){
        <li>{{$responsible->contact->name}} as {{$responsible->type->name}}</li>
    }
</ul>