In this installment of our games app, we are going to add a sidebar for the main games and reviews views. In other words, when the site user is listing all games to view or all reviews to see, there will be a sidebar displayed. This sidebar will hold information about the games and reviews that have been submitted by users of the site. Specifically, we would like to show the user who has submitted the most games first, the user who has submitted the second most games second, and so on. It’s a way to feature so to speak, the active contributors to the site. How can we do this? We are going to do this by making use of raw sql queries combined with Eloquent. Let’s see how to do this now.
Figure Out Your Raw Query First
Ok here’s the deal Eloquent fans. When you need to perform queries that start to get a little more than just simply fetching all records in the database, you’re going to need to know what the actual query you’re trying to send to the database is, before you can use Eloquent to perform the action for you. If you don’t have a grasp of how to construct sql queries on your own, trying to create queries using Eloquent is only going to frustrate you. With that said, what are we trying to do with our query?
What do you want, in natural English
We want to get the users from the database who have submitted the most games, and place the most active submitter at the top of our results. Let’s figure this out in raw MySql first. For this, we will make use of DataGrip which is a fantastic database tool brought to you by JetBrains. JetBrains is also the maker of PHP Storm, a great piece of software.
First off, we need the user names. We can do this with a simple SELECT statement.
Next, we need information about the games each user submitted.
Hmm, we might have a small problem. We are selecting data from the users table, and we now have the names of users. This is helpful, but in the users table, there is nothing about games or which games a user might have submitted. The games table however, does have information about the games that users have submitted. It looks like we are trying to get information from two tables at the same time. Are we stuck? No we are not. In a case like this, you want to make use of a JOIN to help you. By using a JOIN statement, you can get data from two tables at once. We will join the games table and the users table on the condition that the user_id field of games is equal to the id field of users.
Ok what happened there? We now get the result of Mario, Mario, Mario, Toad, Luigi, and Toad. Not really what we need just yet. We need to somehow count the number of games each user submitted. For this, we are going to need to make use of the aggregate function count() and the GROUP BY clause. Here, we must specify a count of all games submitted and rename the temporary table as an alias of submitted_games. With that complete, we would need to group those results by the user’s names. Let’s see:
We’re getting close! Now we can see that Luigi has submitted 1 game, Mario has submitted 3 games, and Toad has submitted 2 games. We want to highlight the most active game submitters however, so we want Mario at the top and Luigi at the bottom. We can just add a handy ORDER BY clause to get this done. What will we order by? We shall order by the number of submitted games in descending order.
Our Final Raw SQL Query
SELECT
users.name,
count(*) submitted_games
FROM users
JOIN games ON games.user_id = users.id
GROUP BY users.name
ORDER BY submitted_games DESC
This is exactly what we want, and taking this small incremental approach to building up database queries is helpful. We now know what we are trying to do, and how to do it with a raw query. Let’s get this into Laravel now.
Convert Your Raw Query into Eloquent
If we convert that raw query that we came up with in Laravel, it may look something like this:
$activeusers = User::selectRaw('users.name, count(*) submitted_games')
->join('games', 'games.user_id', '=', 'users.id')
->groupBy('users.name')
->orderBy('submitted_games', 'DESC')
->get();
Add the query to the index() method on Games Controller
When we visit the /games route, recall that this fires the index() method on the Games Controller. We’d like to be able to see this sidebar when viewing all games, so we are going to need to fetch the most active users from the database in this method. We can update the Games Controller like so:
app/Http/Controllers/GamesController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Game;
use App\User;
class GamesController extends Controller
{
public function __construct()
{
$this->middleware('auth')->except(['index', 'show']);
}
public function index()
{
$games = Game::latest()->get();
$activeusers = User::selectRaw('users.name, count(*) submitted_games')
->join('games', 'games.user_id', '=', 'users.id')
->groupBy('users.name')
->orderBy('submitted_games', 'DESC')
->get();
return view('games.index', ['games' => $games, 'activeusers' => $activeusers]);
}
public function show(Game $game)
{
return view('games.show', ['game' => $game]);
}
public function create()
{
return view('games.create');
}
public function store()
{
$this->validate(request(), [
'title' => 'required|unique:games',
'publisher' => 'required',
'releasedate' => 'required',
'image' => 'required',
]);
$game = new Game;
$game->title = request('title');
$game->publisher = request('publisher');
$game->releasedate = request('releasedate');
$game->image = request()->file('image')->store('public/images');
$game->user_id = auth()->id();
$game->save();
return redirect('/games');
}
}Update the games/index.blade.php view file
Our current view file for listing all games does so with one simple column. We need to change this up just a bit to allow for room on the side to display a sidebar which will list our new most active users section. Here is how we updated that file. Note that we are extracting the actual markup inside the sidebar to it’s own partial.
resources/views/games/index.blade.php
@extends('layouts.master')
@section('content')
<div class="row">
<div class="col-8">
@foreach($games as $game)
<div class="mb-3">
<div class="card">
<div class="card-block">
<h3 class="card-title"><a href="/games/{{ $game->id }}">{{ $game->title }}</a></h3>
<p class="card-text">Published by {{ $game->publisher }}</p>
<p class="small">Game submitted by
user {{ $game->user->name }} {{ $game->created_at->diffForHumans() }}</p>
<a href="/games/{{ $game->id }}" class="btn btn-primary">Learn More</a>
</div>
</div>
</div>
@endforeach
</div>
<div class="col-4">
@include('partials.activeusers')
</div>
</div>
@endsectionresources/views/partials/activeusers.blade.php
The markup for the partial view file.
<table class="table table-sm table-hover">
<thead class="thead">
<tr>
<th>User Name</th>
<th>Games Submitted</th>
</tr>
</thead>
<tbody>
@foreach($activeusers as $activeuser)
<tr>
<td>{{ $activeuser->name }}</td>
<td>{{ $activeuser->submitted_games }}</td>
</tr>
@endforeach
</tbody>
</table>Looking Good!
Display Most Active Users on Reviews Page
It might also be cool to show the top submitters on the site when users visit /reviews as well. So if they are viewing all games, or all reviews, they see a sidebar with the top game submitters. Looks like we’ll need to update the Reviews Controller then in addition to the reviews/index.blade.php file. Let’s update those so we can get the goodness on both pages.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Game;
use App\Review;
use App\User;
class ReviewsController extends Controller
{
public function __construct()
{
$this->middleware('auth')->except(['index', 'show']);
}
public function index()
{
$reviews = Review::latest()->get();
$activeusers = User::selectRaw('users.name, count(*) submitted_games')
->join('games', 'games.user_id', '=', 'users.id')
->groupBy('users.name')
->orderBy('submitted_games', 'DESC')
->get();
return view('reviews.index', ['reviews' => $reviews, 'activeusers' => $activeusers]);
}
public function create(Game $game)
{
return view('reviews.create', ['game' => $game]);
}
public function store(Game $game)
{
$this->validate(request(), [
'body' => 'required|min:3'
]);
$game->addReview(request('body'), auth()->id());
return redirect()->to('/games/' . request()->route()->game->id);
}
public function show(Review $review)
{
return view('reviews.show', ['review' => $review]);
}
}resources/views/reviews/index.blade.php
@extends('layouts.master')
@section('content')
<div class="row">
<div class="col-8">
@foreach($reviews as $review)
<div class="col-12 mb-3">
<div class="card">
<div class="card-block">
<p class="card-text">{{ $review->user->name }} left a <a href="/reviews/{{$review->id}}">review</a>
for <a
href="/games/{{ $review->game->id }}">{{ $review->game->title }}</a> {{$review->created_at->diffForHumans()}}
</p>
</div>
</div>
</div>
@endforeach
</div>
<div class="col-4">
@include('partials.activeusers')
</div>
</div>
@endsectionWe now have the most active users displaying on both the /reviews and /games endpoints.
Refactor Time
Those queries in our controller methods are not the best looking. We must fix this with vigor and tenacity at once. Let us extract this functionality elsewhere, where it can hide it’s complexity in the shadows.
First we will write out the code, as we would like to have it in the controller. It would be nice if we could simply fetch our active game submitters with something as simple as $activeusers = User::activeusers(); In fact, we will update the Games Controller as such right now.
app/Http/Controllers/GamesController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Game;
use App\User;
class GamesController extends Controller
{
public function __construct()
{
$this->middleware('auth')->except(['index', 'show']);
}
public function index()
{
$games = Game::latest()->get();
$activeusers = User::activeusers();
return view('games.index', ['games' => $games, 'activeusers' => $activeusers]);
}
public function show(Game $game)
{
return view('games.show', ['game' => $game]);
}
public function create()
{
return view('games.create');
}
public function store()
{
$this->validate(request(), [
'title' => 'required|unique:games',
'publisher' => 'required',
'releasedate' => 'required',
'image' => 'required',
]);
$game = new Game;
$game->title = request('title');
$game->publisher = request('publisher');
$game->releasedate = request('releasedate');
$game->image = request()->file('image')->store('public/images');
$game->user_id = auth()->id();
$game->save();
return redirect('/games');
}
}Update the User Model
We must now actually define the activeusers() method on our User model in order for it to work in our Games Controller. Here is how we can make that happen.
app/User.php
<?php
namespace App;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
class User extends Authenticatable
{
use Notifiable;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name', 'email', 'password',
];
/**
* The attributes that should be hidden for arrays.
*
* @var array
*/
protected $hidden = [
'password', 'remember_token',
];
/**
* Add a mutator to ensure hashed passwords
*/
public function setPasswordAttribute($password)
{
$this->attributes['password'] = bcrypt($password);
}
public function games()
{
return $this->hasMany(Game::class);
}
public function reviews()
{
return $this->hasMany(Review::class);
}
public static function activeusers()
{
return static::selectRaw('users.name, count(*) submitted_games')
->join('games', 'games.user_id', '=', 'users.id')
->groupBy('users.name')
->orderBy('submitted_games', 'DESC')
->get();
}
}app/Http/Controllers/ReviewsController.php
We can now make our Reviews Controller pretty as well.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Game;
use App\Review;
use App\User;
class ReviewsController extends Controller
{
public function __construct()
{
$this->middleware('auth')->except(['index', 'show']);
}
public function index()
{
$reviews = Review::latest()->get();
$activeusers = User::activeusers();
return view('reviews.index', ['reviews' => $reviews, 'activeusers' => $activeusers]);
}
public function create(Game $game)
{
return view('reviews.create', ['game' => $game]);
}
public function store(Game $game)
{
$this->validate(request(), [
'body' => 'required|min:3'
]);
$game->addReview(request('body'), auth()->id());
return redirect()->to('/games/' . request()->route()->game->id);
}
public function show(Review $review)
{
return view('reviews.show', ['review' => $review]);
}
}Like A Boss
How To Select Raw SQL Using Eloquent Summary
In this tutorial, we had a look at how you can incorporate raw sql queries into your Eloquent code. This is sometimes needed when you want to apply more granular type queries that might involve alias tables or other very specific type queries. During this process, we saw that it makes sense to figure out what you are actually trying to get from the database in plain English. Then you can translate this to actual sql code to see if it is returning the data you are looking for. Once you are happy, you can convert this query into Eloquent and test it out in your application. In our case, we made use of the selectRaw() method to insert raw sql into our query where needed. With this approach, we came up with a custom query to find the most active submitters of games ordered by the top submitters so we could create a widget to display on our games website. Very cool!
What Are Django Field Lookups
Crud In Laravel 4
How To Use The Laravel Query Builder
How To Validate Form Submissions In Laravel
Advanced Table Configuration in SQLAlchemy: Adding Columns, Relationships, and Constraints
Does Python Support Cloud-Based Databases Like AWS RDS and Google Cloud SQL
What Are the Differences Between Python Lists, Tuples, Dictionaries, and Sets
Data Types in MySQL