Updated April 11, 2023
Introduction to Laravel Query Builder
One of the most important functionalities of the Laravel Framework is Query Builder. The Laravel framework’s database query builder offers a convenient and fluent interface for creating and operating database queries. It can be used to accomplish most database operations in the application and performs on all Laravel Framework-supported database systems. It is based on the PDO parameter binding to protect the application against any SQL injection attacks. The advantage on gets here is that there is no need to clean the strings being passed as bindings.
Few examples of how a query looks like:
1. To fetch users from the said table.
Code:
$users = DB::table('users')->get();2. To fetch a single row from the said table.
Code:
$user = DB::table('users')->where('name', 'John')->first();3. To fetch John and his ID.
$user_id = DB::table('users')->where('name', 'John')->pluck('id');4. To fetch a few columns.
Code:
$users = DB::table('users')->select('name', 'email')->get();5. To fetch user ids which are less than 10.
Code:
$users = DB::table('users')->where(id, '<', 10)->get();6. To fetch ids which are numbered between 10 and 20.
Code:
$users = DB::table('users')->whereBetween('id', array(10, 20))->get()While talking about the Laravel Query builder, it should also be needed to know about the ORM. ORM or Object Relational Mapper is a method to connect to objects without having to consider the relationship of the object to their source. The ORM in Laravel is called the Eloquent. Eloquent allows the developer to connect the database object with the relationship using an easy to use syntax. Eloquent ORM delivers Active Record execution which means that each model we create in the MVC structure relates to a table in the database.
Examples
Given below are the examples:
Example #1
To show all the rows from a distinct column.
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$students = DB::table('students')->select('name', 'email as student_email')->get();
$table="<table border='1' width='300'";
$table.="<tr><th>Name</th><th>Email</th></tr>";
foreach($students as $student){
$table.="<tr><td>$student->name</td><td>$student->student_email</td></tr>";
}
$table.="</table>";
echo $table;
}
}Output:
Example #2
To choose only the unique values and leaving the duplicated values aside”.
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$students = DB::table('students')->distinct()->get(["name"]);
foreach($students as $student){
echo $student->name,"<br>";
}
}
}Output:
Example #3
Addition of a new column.
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$query = DB::table('students')->select('name');
$students = $query->addSelect('email')->get();
$table="<table border='1' width='300'";
$table.="<tr><th>Name</th><th>Email</th></tr>";
foreach($students as $student){
$table.="<tr><td>$student->name</td><td>$student->email</td></tr>";
}
$table.="</table>";
echo $table;
}
}Output:
Example #4
Executing the Inner Join() query function.
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$students = DB::table('students')
->join('contacts', 'students.id', '=', 'contacts.student_id')
->join('course_choice', 'students.id', '=', 'course_choice.student_id')
->join('courses', 'students.id', '=', 'course_choice.student_id')
->select('students.id','students.name','courses.course_name','contacts.phone','contacts.email')
->get();
echo "<pre>";
print_r($students);
echo "</pre>";
}
}Output:
Example #5
Executing the Advanced Join clause.
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$students=DB::table('students')
->join('contacts', function ($join) {
$join->on('students.id', '=', 'contacts.student_id')
->where('contacts.student_id', '>', 3);
})
->get();
echo "<pre>";
print_r($students);
echo "</pre>";
}
}Output:
Example #6
With the Laravel Query Builder you can execute the Union query.
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$first = DB::table('contacts')
->whereNull('phone');
$students = DB::table('contacts')
->whereNull('email')
->union($first)
->get();
echo "<pre>";
print_r($students);
echo "</pre>";
}
}Output:
Example #7
Executing the update() method.
laravel>php artisan make:controllerStudUpdateController–plain
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;
class StudUpdateController extends Controller
{
public function index()
{
$users = DB::select('select * from student');
return view('stud_edit_view',['users'=>$users]);
}
public function show($id)
{
$users = DB::select('select * from student where id = ?',[$id]);
return view('stud_update',['users'=>$users]);
}
public function edit(Request $request,$id)
{
$name = $request->input('stud_name');
DB::update('update student set name = ? where id = ?',[$name,$id]);
echo "Record updated successfully.<br/>";
echo '<a href = "/edit-records">Click Here</a> to go back.';
}
}
<html>
<head>
<title>View Student Records</title>
</head>
<body>
<table border = “1”>
<tr>
<td> ID </td>
<td> Name </td>
<td> Edit </td>
</tr>
@foreach ($users as $user)
<tr>
<td>{{ $user->id }} </td>
<td>{{ $user->name }} </td>
<td><a href = ‘edit/{{ $user->id }}’>Edit</a></td>
</tr>
@endforeach
</table>
</body>
</html>
<html>
<head>
<title>Student Management | Edit</title>
</head>
<body>
<form action = “/edit/<?php echo $users[0]->id; ?>” method = “post”>
<input type = “hidden” name = “_token” value = “<?php echo csrf_token(); ?>”>
<table>
<tr>
<td> Name </td>
<td>
<input type = ‘text’ name = ‘stud_name’
value = ‘<?phpecho$users[0]->name; ?>’/></td>
</tr>
<tr>
<td colspan = ‘2’>
<input type = ‘submit’ value = “Update student” /></td>
</tr>
</table>
</form>
</body>
</html>
Route::get('edit-records','StudUpdateController@index');
Route::get('edit/{id}','StudUpdateController@show');
Route::post('edit/{id}','StudUpdateController@edit');Output:
After clicking on the edit option:
After the operation has been successfully operated:
The advantage of using the Laravel Query builder is that it eases the steps involved and executes the task in quicker time. This helps the developer in implementing it at a faster rate. It is the premise for most of Laravel’s functionality building.
Conclusion
Laravel Framework is one of the most sought after of frameworks because of the sheer scale of option it provides to the client. With Laravel Query Builder one can execute all the basic queries relevant to get the desired output or what the project necessitates.
Recommended Articles
We hope that this EDUCBA information on “Laravel Query Builder” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
