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

book_issues

and student

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

if you want to see other files I'll give you just ask who can help me?

CodePudding user response：

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