CODEWITHSUNDEEP
laravellaravel-5laravel-5.3laravel-5.4
user8203594
user8203594

Reputation:

How to join tables in laravel 5.4

In my case, I have 3 tables like Question, options, and answers

Questions table

|id      |     question_name|
------------------------------
      1          question1
      2          question2 
      3          question3

options table

id  |     question_id     |   options |
----------------------------------------
1           1                   option1
----------------------------------------
1           1                   option2
----------------------------------------
1           1                   option3
----------------------------------------
1           1                   option4

Answers table

id   |   customer_id |   question_id | answer(selected by user) |
--------------------------------------------------------------------
 1               1              1               option1          
--------------------------------------------------------------------
 1               2              2               option2            
--------------------------------------------------------------------
 1               1              3               option3    
--------------------------------------------------------------------
 1               1              3               option2

How can I get below output from answers using joins table
For customer 1

 question1
    --option1
 question2
    --option2
 question3
    --option3
    --option2

I have eloquent relation,

 Question model 
    class Question extends Model
    {
        public function options()
        {
            return $this->hasMany(Option::class);
        }
        public function customer()
        {
            return $this->belongsTo(CustomerProfile::class);
        }
        public function answers()
       {
            return $this->hasMany(Answer::class);
       }
    }

  Option model
      public function question()
      {
        return $this->belongsTo(Question::class);
      }

   Answer model 
      public function customer()
      {
          return $this->belongsTo(CustomerProfile::class);
      }
      public function question()
      {
          return $this->belongsTo(Question::class);
      }

That is how my relationships looks like, Now I just need to join the tables to get output.

Upvotes: 0

Views: 806

Answers (3)

Rwd
Rwd

Reputation: 35180

Going off the comment you left on Aaron Fahey's answer, you'll need to add a constraint to the query and the eager-load:

$customerId = 1; 

$questions = Question::with([
    'options', 'answers' => function ($query) use ($customerId) {
        $query->where('customer_id', $customerId);
    }])
    ->whereHas('answers', function ($query) use ($customerId) {
        $query->where('customer_id', $customerId);
    })
    ->get();

https://laravel.com/docs/5.4/eloquent-relationships#constraining-eager-loads

https://laravel.com/docs/5.4/eloquent-relationships#querying-relationship-existence

Hope this helps!

Upvotes: 0

asmmahmud
asmmahmud

Reputation: 5044

I guess, you also have defined eloquent relations in Customer Model class. If you do so, then you can retrieve all the answers of a particular customer through customer model and this will give you the hook to get the answer's question and its all options:

$customer = Customer::find($customerId);
$answer = $customer->answers()->where('id', $answerId)->get();

$question = $answer->question;

$questionOptions = $question->options;

I hope it will help.

Upvotes: 0

Aaron Fahey
Aaron Fahey

Reputation: 699

In order to get the related options for your questions, you can use eager loading.

An example would be:

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

You will then have a collection with the questions, and their related options. You will need to construct a loop to get the data you want.

Upvotes: 1

Related Questions