In this tutorial, we'll add a new page called "Leaderboard" to show the top 100 results. Also, we'll let users choose which quiz to show.
For this, we need another Livewire component.
php artisan make:livewire Front/LeaderboardFirst, let's show leaderboard and later we will add ability to select quiz. For the query we will need to access question_quiz pivot table. For this we'll create a model.
php artisan make:model QuestionQuizapp/Models/QuestionQuiz.php:
class QuestionQuiz extends Model{ protected $table = 'question_quiz';}We need a route and add it to the navigation.
routes/web.php:
use App\Livewire\Front\Leaderboard; Route::get('/', [HomeController::class, 'index'])->name('home');Route::get('quiz/{quiz}/{slug?}', [HomeController::class, 'show'])->name('quiz.show');Route::get('results/{test}', [ResultController::class, 'show'])->name('results.show');Route::get('leaderboard', Leaderboard::class)->name('leaderboard'); // ...resources/views/layouts/navigation.blade.php:
// ...<!-- Navigation Links --><div class="hidden space-x-8 sm:-my-px sm:ml-10 sm:flex"> <x-nav-link :href="route('leaderboard')" :active="request()->routeIs('leaderboard')"> Leaderboard </x-nav-link> @auth <x-nav-link :href="route('results.index')" :active="request()->routeIs('results.index')"> My Results </x-nav-link> @endauth</div>// ...Now the Livewire component.
app/Livewire/Front/Leaderboard.php:
class Leaderboard extends Component{ public Collection $quizzes; public function mount(): void { $this->quizzes = Quiz::where('public', 1)->where('published', 1)->get(); } public function render(): View { $total_questions = QuestionQuiz::select('question_id') ->join('quizzes', 'question_quiz.quiz_id', '=', 'quizzes.id') ->where('quizzes.published', 1) ->count(); $users = User::select('users.name', \DB::raw('sum(tests.result) as correct'), \DB::raw('sum(tests.time_spent) as time_spent')) ->join('tests', 'users.id', '=', 'tests.user_id') ->whereNotNull('tests.quiz_id') ->whereNotNull('tests.time_spent') ->whereNull('tests.deleted_at') ->groupBy('users.id', 'users.name') ->orderBy('correct', 'desc') ->orderBy('time_spent') ->get(); return view('livewire.front.leaderboard', compact('users', 'total_questions')); }}When the component gets mounted we get all the quizzes the are public and published. From those quizzes then we get total questions count and users. Then we show them in the list.
resources/views/livewire/front/leaderboard.blade.php:
<div> <x-slot name="header"> <h2 class="font-semibold text-xl text-gray-800 leading-tight"> Leaderboard </h2> </x-slot> <div class="py-12"> <div class="max-w-7xl mx-auto sm:px-6 lg:px-8"> <div class="bg-white overflow-hidden shadow-sm sm:rounded-lg"> <div class="p-6 text-gray-900"> <table class="table mt-4 w-full table-view"> <thead> <tr> <th class="bg-gray-50 px-6 py-3 text-left w-9"></th> <th class="bg-gray-50 px-6 py-3 text-left w-1/2"> <span class="text-xs font-medium uppercase leading-4 tracking-wider text-gray-500">Username</span> </th> <th class="bg-gray-50 px-6 py-3 text-left"> <span class="text-xs font-medium uppercase leading-4 tracking-wider text-gray-500">Correct answers</span> </th> </tr> </thead> <tbody> @forelse ($users as $user) <tr @class(['bg-slate-100' => auth()->check() && $user->name == auth()->user()->name])> <td class="px-6 py-4 text-sm leading-5 text-gray-900 whitespace-no-wrap"> {{ $loop->iteration }} </td> <td class="px-6 py-4 text-sm leading-5 text-gray-900 whitespace-no-wrap"> {{ $user->name }} </td> <td class="px-6 py-4 text-sm leading-5 text-gray-900 whitespace-no-wrap"> {{ $user->correct }} / {{ $total_questions }} (time: {{ intval($user->time_spent / 60) }}:{{ gmdate('s', $user->time_spent) }} minutes) </td> </tr> @empty <tr> <td colspan="3">No results.</td> </tr> @endforelse </tbody> </table> </div> </div> </div> </div></div>Now the leaderboard should look similar like below:
Next, let's add a select input to select which quiz results to show. Just before table add a select input.
resources/views/livewire/front/leaderboard.blade.php:
<div> <x-slot name="header"> <h2 class="font-semibold text-xl text-gray-800 leading-tight"> Leaderboard </h2> </x-slot> <div class="py-12"> <div class="max-w-7xl mx-auto sm:px-6 lg:px-8"> <div class="bg-white overflow-hidden shadow-sm sm:rounded-lg"> <div class="p-6 text-gray-900"> <select class="p-3 w-full text-sm leading-5 rounded border-0 shadow text-slate-600" wire:model.blur="quizId"> <option value="0">--- all quizzes ---</option> @foreach($quizzes as $quiz) <option value="{{ $quiz->id }}">{{ $quiz->title }}</option> @endforeach </select> <table class="table mt-4 w-full table-view">// ...
We bind it to quizId public property. Using this property we need to filter results.
app/Livewire/Front/Leaderboard.php:
class Leaderboard extends Component{ public Collection $quizzes; public int $quizId = 0; public function mount(): void { $this->quizzes = Quiz::where('public', 1)->where('published', 1)->get(); } public function render(): View { $total_questions = QuestionQuiz::select('question_id') ->join('quizzes', 'question_quiz.quiz_id', '=', 'quizzes.id') ->where('quizzes.published', 1) ->when($this->quizId > 0, function ($query) { return $query->where('quiz_id', $this->quizId); }) ->count(); $users = User::select('users.name', \DB::raw('sum(tests.result) as correct'), \DB::raw('sum(tests.time_spent) as time_spent')) ->join('tests', 'users.id', '=', 'tests.user_id') ->whereNotNull('tests.quiz_id') ->whereNotNull('tests.time_spent') ->whereNull('tests.deleted_at') ->when($this->quizId > 0, function ($query) { return $query->where('tests.quiz_id', $this->quizId); }) ->groupBy('users.id', 'users.name') ->orderBy('correct', 'desc') ->orderBy('time_spent') ->take(100) ->get(); return view('livewire.front.leaderboard', compact('users', 'total_questions')); }}