Ad

Laravel Many-to-many Relationship With Multiple Custom Pivot Tables?

I hope I'm not asking an already answered question, but using all the search terms I can think of I can't work out a solution to this:

I have two tables, companies and respondents

Sometimes, a respondent can select one or more companies. A company can be selected by one or more respondents.

Other times, a respondent can rate one or more companies. A company can be rated by one or more respondent.

It is possible for a respondent to both select and rate a company. (Indeed, all companies that a respondent rates they have to have selected, but they don't rate all the companies they select).

As a further complication, a respondent is an employee of one company only, and is also nominated by one or more companies to to the selecting and rating.

My line of thinking is to have multiple custom pivots:

//relationship between 'companies' and 'respondents' to show who they selected 
selected_companies 

//relationship between 'companies' and 'respondents' to show who they rated 
rated_companies 

//relationship between 'companies' and 'respondents' to show which companies a respondent was nominated by 
nominating_companies

//relationship between 'companies' and 'respondents' to show who a respondent is employed by
employment

I guess the last one is a simple one to many, so I can just put a custom named FK of employer_id in the respondents table.

Other than that, I'm pretty stuck as to how to implement this. I know that custom intermediate pivot models is a thing, but I also can't figure out how to implement that.

Ad

Answer

OK, so here's how I dealt with this.

Custom pivot table

In the pivot, I added a column called type. This is what my company_respondent pivot table migration now looks like:

    Schema::create('company_respondent', function (Blueprint $table) {
        $table->unsignedInteger('company_id');
        $table->unsignedInteger('respondent_id');
        $table->string('type');

        $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
        $table->foreign('respondent_id')->references('id')->on('respondents')->onDelete('cascade');
        $table->primary(['company_id','respondent_id','type']);
    });

Note that for the primary key I am using all three columns. This will allow me to declare different types of relationship between the same company-respondent pair, e.g. when the respondent has selected a company I can store selected and when they have rated a company I can store rated in the type column.

withPivot

Before I can do this, I need to tell Laravel to expect this new column in the Company and Respondent Models using withPivot() when defining the relationship. I need to do this on both sides of the relationship:

//Respondent.php

    use App\Company;

    public function companies() 
    {
        return $this->belongsToMany(Company::class)->withPivot('type');
    }


//Company.php

    use App\Respondent;

    public function respondents() 
    {
        return $this->belongsToMany(Respondent::class)->withPivot('type');
    }

That done, I can now store into that column when saving a relationship, and filter using it.

Storing:

$respondent->companies()->attach($companies_selected, ['type'=> 'selected']);

Where $companies_selected is either a single id or an array of ids.

Filtering:

//returns an array of company names that a respondent with an id of `2` has selected.

$company_names = Respondent::find(2)
                    ->companies()
                    ->wherePivot('type','selected')
                    ->pluck('name')->toArray();

I can simply substitute selected, rated, nominated or anything else that I like to define the different types of relationships that can exist between the two tables.

I hope this helps someone else, or I get feedback on a better way to do this.

Ad
source: stackoverflow.com
Ad