Linking 3 tables in Eloquent?

Ok guys here is the Code for my tables, i hope anyone can help me:

Schema::create('departments', function (Blueprint $table) {
        $table->increments('id');
        $table->nullableTimestamps();
        $table->string('comment')->nullable();
        $table->string('name');
        $table->timestamp('valid_start')->useCurrent();
        $table->timestamp('valid_end')->nullable();
    });

Schema::create('contacts', function (Blueprint $table) {
        $table->increments('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('uid');
        $table->string('comment');
        $table->timestamp('valid_start')->useCurrent();
        $table->timestamp('valid_end')->nullable();
        $table->timestamps();
    });

Schema::create('contact_types', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('comment');
        $table->timestamp('valid_start')->useCurrent();
        $table->timestamp('valid_end')->nullable();
        $table->timestamps();
    });

    Schema::create('contact_contact_type_department', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('contact_id')->unsigned();
        $table->integer('department_id')->unsigned();
        $table->integer('contact_role_id')->unsigned();
        $table->string('comment');
        $table->timestamp('valid_start')->useCurrent();
        $table->timestamp('valid_end')->nullable();
        $table->timestamps();

        $table->foreign('contact_id')->references('id')->on('contacts');
        $table->foreign('department_id')->references('id')->on('departments');
        $table->foreign('contact_type_id')->references('id')->on('contact_types');
    });

Using a model as median, which "belongsTo" each one of the contact/type/department model

This is the way to go. What is the problem with this ?

I have done this now, the problem is, that i have to filter the results based on the relations table (DepartmentResponsible Model). Since i only want to get the valid entrys for the time being (valid_start in the past, valid_end either in the future or null).

I have tried the following:

public function contacts(){
    return $this->hasManyThrough('cap\Contact', 'cap\DepartmentResponsible', 'department_id', 'id')
        ->withoutGlobalScopes()
        ->whereHas('cap\DepartmentResponsible', function($q){
            $now = Carbon::now();
            $q  ->where('valid_end', '>', $now)
                ->orWhere('valid_end', '=', null)
                ->where('valid_start', '<=', $now);
        });

but i get

BadMethodCallException with message 'Call to undefined method Illuminate\Database\Query\Builder::cap\DepartmentResponsible()'

Has many through is absolutely not made for this structure. It's purpose is to chain two has many relationships.

Here I think you can do many belongsToMany relationships with the contact_department as a pivot table. But I don't understand anything about your table/model naming so I'm not sure how to help.

Thank you pmall.

The basic concept behind my DB structure is that the relation table is that it shows who is in which role responsible for which department (plus the validity dates).

I have tried the concept with the pivot first, but i couldnt get that to work, since i had 3 foreign key ids in there. My understanding was that that's impossible, is it, or did i just have a mistake somewhere?

thanks very much :-)

I'd like to help but I don't understand the table naming/model naming. The first step vould be to simplify all this and to stick to laravel naming conventions.

ok... well since english is not my first language im happy to take suggestions to change the names:

contacts - a list of people

contact_types - possible types of contacts, like "IT-contact" or "business - responsible"

departments - i think this one talks for its own

and for the pivot... i tried to stick with the naming convetnions first, but "contact_contact_type_department" didn't sound right to me.

I then chose DepartmentResponsible as name for the relationmodel

And what do you want to select and display exactly?

in this case i (for example) want to display all the contacts (ideally including their role) for a department.

OR (another example)

list all the departments (ideally including their role again) a person is a contact for

(wtf is role here you didn't mentioned it when you explained the tables. That why I'm lost)

first case you want to achieve this :

$department = Department::with('roles.contact')->findOrFail($department_id);

foreach ($department->roles as $role) {

    $role->contact;

}

So here I just see a department table, a role table and a contact table. Department hasMany Role and Role belongsTo Contact.

2nd case :

$contact = Contact::with('roles.department')->findOrFail($contact_id);

foreach ($contact->roles as $role) {

    $role->department;

}

Same thing as above

oh sorry, i changed role to contact_type my bad (i have updated the description now).

So what do i have to do in order to make that work? Create a pivot table with the laravel naming standards and linking them with a hasmany or belongstomany?

Do i understand your query correctly at "'roles.department'", that you are looking at a department column on the roles (=types) table? because there isn't any...

I am sorry to ask so many stupid questions, im fairly new to laravel...