Laravel: How to Use Multiple Pivot Table Relationships

Laravel: How to use multiple pivot table relationships

This setup should get you going. I tried to keep the naming as simple as possible.

users
    id
    username

challenge_user
    user_id
    challenge_id

challenges
    id
    name
    topic_id      
    category_id

topics
    id
    name

categories
    id
    name

Defining your Eloquent Models

class User extends Eloquent {
    public function challenges() {
        return $this->belongsToMany('Challenge');
    }
}

class Challenge extends Eloquent {
    public function users() {
        return $this->belongsToMany('User');
    }
    public function topic() {
        return $this->belongsTo('Topic');
    }
    public function category() {
        return $this->belongsTo('Category');
    }
}

class Topic extends Eloquent {
    public function challenges() {
        return $this->hasMany('Challenge');
    }
}

class Category extends Eloquent {
    public function challenges() {
        return $this->hasMany('Challenge');
    }
}

Using your Eloquent Models ... just some examples of what you can do.

// Collection of all Challenges by Topic name
Topic::with('challenges')->whereName($topic_name)->first()->challenges;

// Collection of all Challenges by Category name
Category::with('challenges')->whereName($category_name)->first()->challenges;

// Collection of all Users by Challenge id
Challenge::with('users')->find($challenge_id)->users;

// Collection of Users with atleast 2 Challenges
User::has('challenges', '>', 1)->get();

// Attach Challenge to User
$user = User::find($id);
$user->challenges()->attach($challenge_id);

// Assign a Topic to a Challenge
$challenge = Challenge::find($challenge_id);
$topic     = Topic::find($topic_id);

$challenge->topic()->associate($topic);
$challenge->save();

References and suggested reading:

Laravel Eloquent Relationships belongsTo belongsToMany hasMany

Querying relations Model::has()

Eager Loading Model::with()

Dynamic Properties for Accessing Relations Resolve $model->relationship

Inserting related Models attach() associate()

Query Scopes

Working with Pivot tables If you need to retrieve extra data from the pivot table.

Laravel multiple pivot relationship

Getting the items that share at least one tag with a given playlist:

$tags = $playlist->tags()->pluck('id');
$items = Item::whereHas('tags', function($query) use($tags) {
    $query->whereIn('id', $tags);
})->get();

Getting the items that share all the tags with a given playlist:

$tags = $playlist->tags()->pluck('id');
$items = Item::whereHas('tags', function($query) use($tags) {
    $query->whereIn('id', $tags);
}, '=', $tags->count())->get();

The reverse direction works the same way.

Laravel many-to-many relationship with multiple custom pivot tables?

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.

Laravel many to many 2 level down pivot table

In this case you can create a Custom Intermediate Table Model (Custom Pivot)

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\Pivot;

class SurveyQuestion extends Pivot
{
    public function options()
    {
        //You need to create a pivot pivot table survey_question_option
        //with (survey_question_id, option_id)
        return $this->belongsToMany(Option::class, 'survey_question_option');
    }
}

Your models will need to recognize this new Pivot Model with the method using(). Here is an example with Survey Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Survey extends Model
{
    /**
     * The users that belong to the role.
     */
    public function questions()
    {
        return $this
            ->belongsToMany(Question::class)
            ->using(SurveyQuestion::class);
    }
}

And them you will be able to access via eloquent like this

$survey->questions->pivot->options

For more information you can check the documentation here:
https://laravel.com/docs/8.x/eloquent-relationships#defining-custom-intermediate-table-models

Laravel 6: Fetching data from multiple pivot tables

Your table naming convention is not as per what Laravel expects: https://laravel.com/docs/7.x/eloquent-relationships#many-to-many

This modified code should return your funds as expected:

class Faculty extends Model
{
  public function funds(){
        return $this -> belongsToMany('App\Fund', 'faculties_funds', 'falculty_id', 'fund_id');
    }
}

class User extends Model{
  public function faculties(){
        return $this -> belongsToMany('App\Faculty','faculties_user');
    }
}

And then call

dd(Auth::user()->with('faculties.funds');

---

Related Topics