Search code examples
laraveleloquentmany-to-manyrelationship

Laravel relations with composite foreign keys

Here's what I want to achieve, we have Staff and every staff can have one Position for a department, as well as one Designation for a department and multiple Additional Charges for multiple departments, one department per Additional Charge. And I want to store all the responsibilities in the staff_responsibilities table

I have a staff table with the following columns

  • id
  • name
  • email

Then I have staff_responsibilities table with the following columns:

  • staff_id
  • designation_id
  • department_id
  • responsibility_type_id

Responsibility types can be Position and Additional Charge and one staff can have multiple designations with Additional Charge responsibility.

Then I have a responsibility_types table with the following columns:

  • id
  • name

// I have no way to tell this relationship to look for a responsibility type 2 (2 is the id of the responsibility called `Designation`) 

public function designation()
    {
        return $this->hasOne(StaffResponsibility::class);
    }

// I have no way to tell this relationship to look for a responsibility type 2 
    public function position()
    {
        return $this->hasOne(StaffResponsibility::class);
    }

// Gives error: Syntax error or access violation: 1066 Not unique table/alias: 'staff_responsibilities' 
    public function additionalCharges()
    {
        return $this->belongsToMany(StaffResponsibility::class, 'staff_responsibilities','designation_id');
    }

Any help would really be appreciated.

staff_responsibilities table

staff_responsibilities

responsibility_types table

responsibility_types table

designations table

designations table

Solution

  • Here's what I want to achieve, we have Staff and every staff can have one Position for a department, as well as one Designation for a department and multiple Additional Charges for multiple departments, one department per Additional Charge.

    It sounds to me like it would be beneficial to create a model for Position, Designation and Additional Charges. It feels like you may need to normalize your database or to change staff_responsibilities as a pivot table.

    But hard to say without knowing your application.

    To answer your question, you could add some scopes in StaffResponsibility

    class StaffResponsibility extends Model{

    public function scopeDesignation($query)
    {
        return $query->where('responsibility_type_id','=',2);
    }

}

    and use it 

    public function designation()
{
    return $this->hasOne(StaffResponsibility::class)->designation();
}

    And the last one is a hasMany relation and not a manyToMany according to what you wrote:

    public function additionalCharges()
{
    return $this->belongsTo(StaffResponsibility::class, 'staff_responsibilities','designation_id')->additionalCharges();
}