php - how to use GroupBy and Where Clause in Laravel Eloquent?
Get the solution ↓↓↓
Please am trying to retrieve data from a joined table in a database using GroupBy and Where clause in laravel. i get errors if i add thegroupBy. it works without the GroupBy but i want to sort the data after retrieving.
this is what i have, someone should kindly assist me
public function showResult($std_id)
{
$results = DB::table('students')
->select('*')
->join('results', 'students.std_id', 'results.student_results_id')
->where('student_results_id', $std_id)
->groupBy('results.academic_year')
->get();
$studentprofilePage = Student::find($std_id);
if (empty($studentprofilePage)) {
return redirect('/user');
}
return view('resultsdisplay', [
'results' => $results,
'studentresultsprofile' => $studentprofilePage,
]);
}
am getting this error
Syntax error or access violation: 1055 'smgt.students.std_id' isn't in GROUP BY (SQL: select * from `students` inner join `results` on `students`.`std_id` = `results`.`student_results_id` where `student_results_id` = 4662416 group by `results`.`academic_year`)
Answer
Solution:
Actually, What I've understood from the code, is that you need to group results by the academic year and then join it to the students table. so I'll suggest
DB::table('students')
->select('*')
->join('results', function ($join) {
$join->on('students.std_id', '=', 'results.student_results_id')->groupBy('results.academic_year');
})
->where('student_results_id', $std_id)
->get();
something like this.
Answer
Solution:
To build onmohammad.kaab answer, you should be able to useStudent model to build this query, which in result will make your code cleaner. I would also recommend utilising Laravel's type hints,showResult(Student $student), Laravel will fetchStudent for you in the background and if a student was not found then 404 is returned.
So you should be able to do something similar to below:
public function showResult(Student $student)
{
$results = $student->results()
->groupBy('results.academic_year');
->get();
return view('resultsdisplay', [
'results' => $results,
'studentresultsprofile' => $student,
]);
}
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: call to a member function update() on null
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.