How to select count with Laravel's fluent query builder?

The fluent query builder in Laravel is an interface that takes care of creating and running the database queries. The query builder works fine with all databases supported in laravel and can be used to perform almost all database operations on it.

The advantage of using a fluent query builder is that it has protection against sql injection attacks. It makes use of PDO parameter binding and you can be free to send your strings as you need.

The fluent query builder supports a lot of methods like count, min, max , avg, sum that fetches you the aggregate values from your table.

Let us now take a look on how to use a fluent query builder to get the count in select query. To work with fluent query builder make use of the DB facade class as shown below

use Illuminate\Support\Facades\DB;

Let us now check a few examples to get the count in the select query. Assume we have created a table named students with 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 populated 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 are 4.

Example 1

In the following example we are using students inside the DB::table. The count() method takes care of 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 from the above example is −

The count of students table is :4

Example 2

In this example will make use of selectRaw() to get the total count 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 count() in the selectRaw() method and pluck is used to fetch the count.

Output

The output of the above code is −

The count of students table is :[4]

Example 3

This example will make use of the selectRaw() method. Consider you want to count names, for example Rehan Khan. Let us see how to use selectRaw() with the 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 count in the table: students with the name: Rehan Khan. So the query written to get that is.

DB::table('students')->where('name', 'Rehan Khan')->selectRaw('count(id) as cnt')->pluck('cnt');

We have made use of the selectRaw() method that takes care of counting the records from the where filter. In the end the pluck() method is used to get the count value.

Output

The output of the above code is −

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

Example 4

Incase you are planning to use count() method to check if any record exists in the table , an alternative to that is you can make use of 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

Using doesntExist() method to check for any record 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