如何使用Eloquent或query Builder执行此查询

Question

我会尽量清楚我想要实现的目标。

所以我有以下4张表。

问题(包含问题名称和ID(

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

AnswerId(包含每个问题的所有可能答案(

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

回复Id(包含回复者Id、问题和他对该问题的回答(

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

话虽如此，我想要实现的是每个问题给出的答案总数，类似于

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

Answer 1

假设您至少有一个Question模型和一个Answer模型，则可以在Question中添加

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

然后你可以简单地调用

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

Answer 2

对不起，伙计，但你在数据库表或集合中建立了错误的关系，在你的第三个表中，应该只有一个带有RespondentID的答案id，而不是questionId和值，但我们可以对当前场景做些什么：

//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

//If you want data as JSON or array format then you can use
$answers = Answer::all();
$response = [];
foreach($answers as $answer){
$response[$answer->Label] = count($answer->responses);
}
//Now you have your requested value in the $response array it looks like
/*
[
Red => 5,
Black => 6
]
*/
//if want in JSON string just use default method
$response = json_encode($response);
//now you have a json string of your data use wherever we need this