我有多个表和我组合他们使用'join'（代码给下面），现在我有一个组合表，它给我正确的答案数量为一个特定的用户和一个特定的测验（给下面）:
我想要添加或想要显示相同用户和测验的组合结果，如下所示:
下面是我的刀片代码:
<thead>
<tr>
<th>Quiz Name</th>
<th>Quiz ID</th>
<th>UserName</th>
<th>User ID</th>
<th>Correct</th>
</tr>
</thead>
<tbody>
@foreach($data_correct as $row)
<tr>
<td>{{$row->qname}}</td>
<td>{{$row->quiz_id}}</td>
<td>{{$row->name}}</td>
<td>{{$row->user_id}}</td>
<td>{{$row->is_correct}}</td>
</tr>
@endforeach
</tbody>
下面是我的控制器函数:
public function leaderboard(){
$quizzes = (new Quiz)->allQuiz();
$users = (new User)->allUsers();
$data_correct = DB::table('results')
->join('answers', 'answers.id', 'results.answer_id')
->join('quizzes', 'quizzes.id', 'results.quiz_id')
->join('users', 'users.id', 'results.user_id')
->select('quizzes.qname', 'results.quiz_id','users.name' ,'results.user_id', 'answers.is_correct')->where('is_correct', 1)
->get();
$data_wrong = DB::table('results')
->join('answers', 'answers.id', 'results.answer_id')
->select('results.quiz_id', 'results.user_id', 'answers.is_correct')->where('is_correct', 0)
->get();
return view('leaderboard', compact('quizzes', 'data_correct', 'data_wrong'));
}
试试这个兄弟
public function leaderboard(){
$quizzes = (new Quiz)->allQuiz();
$users = (new User)->allUsers();
$data_correct = DB::table('users')
->join('answers', 'answers.id', 'results.answer_id')
->join('quizzes', 'quizzes.id', 'results.quiz_id')
->join('results', 'results.user_id', 'user.id')
->select(
'quizzes.qname',
DB::raw('SUM(is_correct) as is_correct FROM answers where answers.id=results.answer_id'),
'users.name' ,
'results.user_id'
)->
where('answers.is_correct', 1)
->get();
$data_wrong = DB::table('results')
->join('answers', 'answers.id', 'results.answer_id')
->select('results.quiz_id', 'results.user_id', 'answers.is_correct')->where('is_correct', 0)
->get();
return view('leaderboard', compact('quizzes', 'data_correct', 'data_wrong'));
}
如果我没看错的话，您可以在select查询中使用变量“quizzes.qname”。 但我只在测验表上看到名字。 请尝试以下操作:
public function leaderboard(){
$quizzes = (new Quiz)->allQuiz();
$users = (new User)->allUsers();
$data_correct = DB::table('results')
->join('answers', function ($join) {
$join->on('answers.id', '=', 'results.answer_id')
->where('answers.is_correct', 1)
})
->join('quizzes', 'quizzes.id', '=', 'results.quiz_id')
->join('users', 'users.id', '=', 'results.user_id')
->select('quizzes.qname', 'results.quiz_id','users.name' ,'results.user_id', DB::raw('count(answers.is_correct) as score'))
->groupBy('results.quiz_id', 'results.user_id')
->get();
$data_wrong = DB::table('results')
->join('answers', function ($join) {
$join->on('answers.id', '=', 'results.answer_id')
->where('answers.is_correct', 0)
})
->select('results.quiz_id', 'results.user_id', DB::raw('count(answers.is_correct) as score'))
->groupBy('results.quiz_id', 'results.user_id')
->get();
return view('leaderboard', compact('quizzes', 'data_correct', 'data_wrong'));
}
在你的评论之后我又调整了一遍。
