Updated April 11, 2023
Introduction to Laravel Raw Query
The Laravel Framework is one of the most sought after of frameworks in recent times. The primary reason being the simplistic approach it nurtures which enables the developer to create a wide variety of functions. The Laravel framework is also known for its robust structure and flexibility. One of the reasons why it is a preferred framework for the eCommerce development community is because of its sheer depth and flexibility. The Laravel framework is also adept at integrating with third-party solutions to create standalone systems. Its query building methodologies are on point and the entire framework is scalable. One of the queries that help us a lot while developing solutions is the Laravel RAW Query.
What is Laravel RAW Query?
The RAW query is to provide a simplistic approach towards executing raw expressions like aggregate. Also, DB:: raw() is used to make an arbitrary SQL command which is not parsed any further by the query builder. Let us look at how Laravel RAW works.
We would start with a simple 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 DESCExplanation: This can be got through the incremental steps that go into building SQL queries. We used Select to get hold of the names of the users. Then we had to make the query know where to look the names from. Hence, the target database was provided. We used JOIN to combine two tables that would now relate the users to the games they had submitted. Then we categorize them consecutively through GROUP BY and ORDER BY. It’s as simple as that.
Output:
Eloquent
Now let us try the same in Eloquent:
Step 1:
$activeusers = User::selectRaw('users.name, count(*) submitted_games')
->join('games', 'games.user_id', '=', 'users.id')
->groupBy('users.name')
->orderBy('submitted_games', 'DESC')
->get();We can go ahead and add the query to the index() method:
Step 2: 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');
}
}A little work on the design part:
Step 3: resources/views/partials/activeusers.blade.php
<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>Step 4: This is part where we are going to show the users who are most active in the review section
<?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]);
}
}Step 5: 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>
@endsectionStep 6: 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');
}
}Step 7: The defining the active user() method is the priority now. It has to be updated so that it will work in the games controller.
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();
}
}Step 8: app/Http/Controllers/ReviewsController.php
We would now have to make the review controllers pretty too.
<?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]);
}
}Output:
Conclusion
The Laravel RAW query is for that kind of developmental projects where a more in depth approach is needed. Laravel RAW allows specificity as well as flexibility.
Recommended Articles
We hope that this EDUCBA information on “Laravel Raw Query” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
