Home > Article > Backend Development > How to select count using Laravel's fluent query builder?
The fluent query builder in Laravel is an interface responsible for creating and running database queries. The query builder works well with all databases supported by Laravel and can be used to perform almost any database operation.
The advantage of using the fluent query builder is that it protects against SQL injection attacks. It utilizes PDO parameter binding and you are free to send strings as needed.
Fluid Query Builder Supports many methods such as count, min, max, avg, sum to get summary values from the table.
Now let’s see how to use the fluent query builder to get the count in a select query. To use the fluent query builder, use the database facade class as shown below
use Illuminate\Support\Facades\DB;
Now let's check a few examples to get counts in select queries. Suppose we create a table named Students using the following query
CREATE TABLE students( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(15) NOT NULL, email VARCHAR(20) NOT NULL, created_at VARCHAR(27), updated_at VARCHAR(27), address VARCHAR(30) NOT NULL );
and fill it as shown below -
+----+---------------+------------------+-----------------------------+-----------------------------+---------+ | id | name | email | created_at | updated_at | address | +----+---------------+------------------+-----------------------------+-----------------------------+---------+ | 1 | Siya Khan | siya@gmail.com | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz | | 2 | Rehan Khan | rehan@gmail.com | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz | | 3 | Rehan Khan | rehan@gmail.com | NULL | NULL | testing | | 4 | Rehan | rehan@gmail.com | NULL | NULL | abcd | +----+---------------+------------------+-----------------------------+-----------------------------+---------+
The number of records in the table is 4.
In the following example, we use students in DB::table. The count() method is responsible for returning the total records present in the table.
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; class StudentController extends Controller{ public function index() { $count = DB::table('students')->count(); echo "The count of students table is :".$count; } }
The output of the above example is -
The count of students table is :4
In this example, selectRaw() will be used to get the total number of records present in the table.
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; class StudentController extends Controller { public function index() { $count = DB::table('students')->selectRaw('count(id) as cnt')->pluck('cnt'); echo "The count of students table is :".$count; } }
The column ID is used inside the count() method of the selectRaw() method, and uses pluck to get the count.
The output of the above code is -
The count of students table is :[4]
This example will use the selectRaw() method. Suppose you want to count the number of names like Rehan Khan. Let's see how to use selectRaw() with count() method
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; class StudentController extends Controller { public function index() { $count = DB::table('students')-> where('name', 'Rehan Khan')-> selectRaw('count(id) as cnt')->pluck('cnt'); echo "The count of name:Rehan Khan in students table is :".$count; } }
In the above example, we want to find the table: Student named Rehan Khan的人数b>. So write the query Just to get it.
DB::table('students')->where('name', 'Rehan Khan')->selectRaw('count(id) as cnt')->pluck('cnt');
We used the selectRaw() method to count records from the where filter. Finally, use the pluck() method to obtain the count value.
The output of the above code is -
The count of name:Rehan Khan in students table is :[2]
If you are planning to use the count() method to check if any record exists in the table, alternatively you can use exists() or doesntExist() method as shown below -
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; class StudentController extends Controller{ public function index() { if (DB::table('students')->where('name', 'Rehan Khan')->exists()) { echo "Record with name Rehan Khan Exists in the table :students"; } } }
The output of the above code is -
Record with name Rehan Khan Exists in the table :students
Use the doesntExist() method to check if there are records available in the given table.
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Illuminate\Support\Facades\DB; class StudentController extends Controller{ public function index() { if (DB::table('students')->where('name', 'Neha Khan')->doesntExist()) { echo "Record with name Rehan Khan Does not Exists in the table :students"; } else { echo "Record with name Rehan Khan Exists in the table :students"; } } }
The output of the above code is -
Record with name Rehan Khan Does not Exists in the table :students
The above is the detailed content of How to select count using Laravel's fluent query builder?. For more information, please follow other related articles on the PHP Chinese website!