Laravel many to many 2 level down pivot table

Question

I am trying to build a Survey Module in Laravel where user can create survey, assign questions and options to the questions in survey.

• survey can have multiple questions
• survey question can have multiple options

To meet the above requirement I created the following models & tables

Model: Question
Table: questions

| id | question                       |
| -------- | -------------------------|
| 1        | How is the performance?  |
| 2        | How did you know about us|

---

Model: Option
Table: options

| id  | option    |
| --- | --------- |
| 1   | Good      |
| 2   | Bad       |
| 3   | Google    |
| 2   | SMS       |

---

Now the relationship between questions and survey will be stored in pivot table

Model: SurveyQuestion
Table: survey_questions

| id | survey_id| question_id |
| ---| -------- |-------------|
| 1  | 1        |1            |
| 1  | 1        |2            |

---

Upto this point I know how to store data into pivot table using attach/sync.

Now the problem is As per requirement each survey question might have multiple options so I created another pivot table survey_question_option in which I am using survey_questions primary key as foreign key.

Model: SurveyQuestionOption
Table: survey_question_options

| id | survey_question_id| option_id |
| -- | ----------------- |-----------|
| 1  | 1                 |1          |
| 1  | 1                 |2          |

---

Now my question is that is it correct to use pivot table primary key as foreign key into another table?

If yes then how can I store data into survey_question_options table using laravel relationships?

If no then what is the better solution?

Answer 1

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