How to perform this query with Eloquent or Query Builder-CodePudding

I'll try to be as clear as possible with what I want to achieve.

So I have the following 4 tables.

Questions ( Which contains the Question Name and ID )

QuestionId                       | SurveyId | FullLabel
036bc25bb3876a25d9961035c1831f49      25      What is your favorite Brand?
35255223cb53e569b450f8a38836425e      25      What is your favorite color?

AnswerId ( Contains all the possible answers for each Question )

AnswerId                          |  QuestionId                        | Label
28aa20eb426ea891cef246eaac99aafb     036bc25bb3876a25d9961035c1831f49    Red
7221fd478768077e54dec4615880eb19     036bc25bb3876a25d9961035c1831f49    Blue
8b7f42e9fd46bfee7fc3f1c471d23be1     036bc25bb3876a25d9961035c1831f49    Green

ResponseId ( Contains the Respondent Id, the Question and the answer he gave to that Question )

ResponseId                       | QuestionId                        |   RespondentID | Value
2e6d15c593332fdf02d835afe016b561   036bc25bb3876a25d9961035c1831f49      25              Blue

Having said that, What I want to achieve is the total of answers gave per Question, something Like this

AnswerLabel                  |      Total
Red                          |         3
Green                        |         4
Blue                         |         2

CodePudding user response：

Assuming you have at least a Question model and an Answer model, in Question you could add

public function answers()
{
    return $this->hasMany(Answer::class);
}

Then you can simply call

Question::withCount('answers')->get();

CodePudding user response：

Pardon me buddy but you made the wrong relationship in your database tables or collection, in your third table there should only be an answer id with the RespondentID instead of questionId and value but leave it what we can do with the current scenario:

//in your question model
public function answers()
{
    return $this->hasMany(Answer::class);
}

//In you answer model
public function responses()
{
    return $this->hasMany(Response::class, 'Value', 'Label')->where('QuestionId', $this->QuestionId);
}

//Finally in your controller get questions and pass them where you want to use in my case let me put these into the blade view
$questions = Question::all();
return view('example', ['questions' => $questions]);

//inside blade view
@foreach($questions as $question)
    @foreach($question->answers as $answer)
        {{ $answer->Label }} | {{ count($answer->responses) }}
    @endforeach
@endforeach