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.

Example 1

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;
   }
}

Output

The output of the above example is -

The count of students table is :4

Example 2

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.

Output

The output of the above code is -

The count of students table is :[4]

Example 3

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. 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.

Output

The output of the above code is -

The count of name:Rehan Khan in students table is :[2]

Example 4

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";
      }
   }
}

Output

The output of the above code is -

Record with name Rehan Khan Exists in the table :students

Example 5

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";
      }
   }
}

Output

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!