Issue
I want to output top readers from a library management system to see who read most this year, but I am stuck with the query building.
I have 3 tables,
books
id | name |
---|---|
1 | name of the book1 |
2 | name of the book2 |
3 | name of the book3 |
4 | name of the book4 |
book_issues
id | student_id | book_id | issue_date |
---|---|---|---|
1 | 1 | 1 | 2022-05-09 |
2 | 2 | 3 | 2022-05-01 |
3 | 3 | 5 | 2022-05-06 |
2 | 2 | 2 | 2022-05-08 |
3 | 2 | 1 | 2022-05-03 |
and student
id | name |
---|---|
1 | name of the student1 |
2 | name of the student2 |
3 | name of the student3 |
4 | name of the student4 |
this is my controller
public function topmonth_wise()
{
return view('report.topmonthWise', ['books' => '']);
}
public function generate_topmonth_wise_report(Request $request)
{
$request->validate(['month' => "required|date"]);
return view('report.topmonthWise', [
'books' => book_issue::where('issue_date', 'LIKE', '%' . $request->month . '%')
// ->select(student::raw('count(book_id) as top'))
//->groupBy('top')
//->orderBy('top', 'DESC')
->limit(10)
//->latest()
->get(),
]);
}
and this is my view:
<table class="content-table">
<thead>
<th>Nr. Crt</th>
<th>Nume elev</th>
<th>Total carti citite</th>
</thead>
<tbody>
@forelse ($books as $book)
<tr>
<td>{{ $book->id }}</td>
<td>{{ $book->student->name }}</td>
<td>{{ $book->count() }}</td>
</tr>
@empty
<tr>
<td colspan="10">Nu s-au găsit înregistrări!</td>
</tr>
@endforelse
</tbody>
</table>
this is my model:
public function student(): BelongsTo
{
return $this->belongsTo(student::class, 'student_id', 'id');
}
/**
* Get the book that owns the book_issue
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function book(): BelongsTo
{
return $this->belongsTo(book::class, 'book_id', 'id');
}
I want to show the top readers of the month in my view but I get an error if I use the code from the controller all I want is the view to show a table like this:
id | student name | total books read |
---|---|---|
1 | student name 2 | 3 |
2 | student name 1 | 1 |
3 | student name 3 | 1 |
if you want to see other files I'll give you just ask who can help me?
Solution
You need to group by student_id
, and then you need to count the total items inside that particular id.
//here we loaded students as well as we need to know the name
//and then group by `student_id`
$book_issues = book_issue::with('student')
->where('issue_date', 'LIKE', '%' . $request->month . '%')
->get()
->groupBy('student_id');
$row = 1;
@foreach($book_issues as $k => $v)
<tr>
<td>{{$row}}</td>
<td>{{$v->first()->student->name}}</td>
<td>{{$$v->count()}}</td>
</tr>
<?php
$row++;
?>
@endforeach
Answered By - Saroj Shrestha Answer Checked By - Clifford M. (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.