4 tables relationship?

Published 2 months ago by curved

Hello people! I am very new to Laravel and watched some videos but pretty confused about Eloquent relationships. Could you please help?

My tables:

  • users:
    • id
    • username
  • questions:
    • id
    • title
  • answers:
    • id
    • question_id
    • text
  • answer_user:
    • id
    • user_id
    • question_id
    • answer_id
class Question extends Model
{

    public function answer()
    {
        return $this->hasMany('App\Answer');
    }
}

class Answer extends Model
{

    public function question()
    {
        return $this->belongsTo('App\Question');
    }

}

class User extends Authenticatable
{
    public function answers()
    {
        return $this->belongsToMany('App\Answer');
    }
}
Best Answer (As Selected By curved)
Snapey

Ok, so if we understand the situation;

Question has many Answer, Answer belongs to just one question

User has many Answer, Answer has many User

Because User and Answer is many to many, a pivot table is required and this should be called answer_user to stick with convention

I would link question via answer, and remove it from the pivot to make it cleaner;

Schema::create('answer_user', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned()->index();
    $table->integer('answer_id')->unsigned()->nullable();
    $table->timestamps();

    $table->foreign('user_id')
          ->references('id')
          ->on('users')
          ->onDelete('cascade');
        
    $table->foreign('answer_id')
          ->references('id')
          ->on('answers')
          ->onDelete('cascade');
});

relationships;

class User extends Authenticatable
{
    public function answers()
    {
        return $this->belongsToMany('App\Answer');
    }
}

class Question extends Model
{
    public function answers()
    {
        return $this->hasMany('App\Answer');
    }

}

class Answer extends Model
{
    public function question()
    {
        return $this->belongsTo('App\Question');
    }

    public function users()
    {
        return $this->belongsToMany('App\User');
    }
}

when you want to list out all questions with possible answers;

$questions = Question::with('answers')->get();

when you want to list all users that chose a specific answer

$answers = Answer::with('users','question')->find(5);

when you want to find all of a user's answers

$answers = $user->answers()->with('question')->get();

when you want to find all answers to a specific question, and who gave that answer;

$answers = Question::with('answers.users')->find(1);

(or if you know the question)

$question->load('answers.users');

hope this helps?

Snapey
Snapey
2 months ago (682,495 XP)
class Question extends Model
{

    public function answers()
    {
        return $this->hasMany('App\Answer');
    }
}

class Answer extends Model
{

    public function question()
    {
        return $this->belongsTo('App\Question');
    }

}

class User extends Authenticatable
{
    public function answers()
    {
        return $this->hasMany('App\Answer');
    }

}
tisuchi
tisuchi
2 months ago (262,165 XP)

My idea is-

  • A user has many questions
  • A question has many answer

Now, you are able to access-

  • all questions with answers and user details.
curved

Thank you guys! @tisuchi do you think the table name should be question_user then? I just want to understand Eloquent and use it

tisuchi
tisuchi
2 months ago (262,165 XP)

The ideal way to write pivot table name is by following alphabetical way [A-Z].

For question and user, its preferably question_user.

You may check this video for better understanding. https://laracasts.com/series/laravel-from-scratch-2017/episodes/30

curved

Did I understand correctly you mean:

class User extends Authenticatable
{
    public function questions()
    {
        return $this->hasMany('App\Question');
    }
}

class Question extends Model
{
    public function answers()
    {
        return $this->hasMany('App\Answer');
    }

    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

class Answer extends Model
{
    public function question()
    {
        return $this->belongsTo('App\Question');
    }
}
tisuchi
tisuchi
2 months ago (262,165 XP)

Yes, you are.

curved

Unknown column 'questions.user_id' I think I'll need my database structure too?

I was thinking a user has many answers & a question has many answers

tisuchi
tisuchi
2 months ago (262,165 XP)

Can you show your database structure?

curved
Schema::create('questions', function (Blueprint $table) {
    $table->increments('id');
    $table->string('title', 255);
    $table->string('type', 8); // Dropdown, Checkbox, Radio
    $table->integer('sort')->unsigned()->default(0);
    $table->timestamps();
});
        
Schema::create('answers', function (Blueprint $table) {
    $table->increments('id');
    $table->string('text');
    $table->integer('question_id')->unsigned();
    $table->timestamps();

    $table->foreign('question_id')
          ->references('id')
          ->on('questions')
          ->onDelete('cascade');
});

Schema::create('answer_user', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned()->index();
    $table->integer('question_id')->unsigned();
    $table->integer('answer_id')->unsigned()->nullable();
    $table->timestamps();

    $table->foreign('user_id')
          ->references('id')
          ->on('users')
          ->onDelete('cascade');
    
    $table->foreign('question_id')
          ->references('id')
          ->on('questions')
          ->onDelete('cascade');
    
    $table->foreign('answer_id')
          ->references('id')
          ->on('answers')
          ->onDelete('cascade');
});
curved

Anyone can help me please?

Snapey
Snapey
2 months ago (682,495 XP)

can you explain what you need to do in just words?

i'm confused how you would like the models to relate

curved

I have questions and answer options belongs to each question. Each user can answer these questions and choose different answers. Since I want to be able to add more questions, I created questions and answers tables. What I need is to store user answers in a pivot table and then I want to be able to search users by answers. For example: find users who chose question "favorite color" red. Or get all questions with user's answers.

I know I can create a model like UserAnswers for the relationships but I just wonder how can I make it Eloquent way.

Snapey
Snapey
2 months ago (682,495 XP)

ok so answers are set by you. they are not the answers given by users

and you need to store the relationship between the user, a question and the answer they chose

curved

Certainly! :) So I can add more questions from my admin panel.

curved

any suggestions @Snapey ?

Please sign in or create an account to participate in this conversation.