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

Laravel's fluent query builder provides an elegant interface for creating and running database queries. It includes built-in protection against SQL injection attacks through PDO parameter binding and supports aggregate methods like count(), min(), max(), avg(), and sum().

To use the fluent query builder, import the DB facade class

use Illuminate\Support\Facades\DB;

Let's explore different methods to get count values using Laravel's query builder. We'll use a students table with the following structure and data ?

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

Using count() Method

The simplest way to get total records count from a 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 count of students table is :4

Using selectRaw() Method

You can use selectRaw() with count() to get more control over the query ?

<?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')
         ->first();
      echo "The count of students table is :".$count;
   }
}
?>

The count of students table is :4

Conditional Count with WHERE Clause

Count specific records based on conditions using where() ?

<?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')
         ->count();
      echo "The count of name:Rehan Khan in students table is :".$count;
   }
}
?>

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

Using exists() Method

For checking record existence, use exists() instead of counting ?

<?php
namespace App\Http\Controllers;
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";
      }
   }
}
?>

Record with name Rehan Khan exists in the table :students

Using doesntExist() Method

Check if records don't exist using doesntExist() ?

<?php
namespace App\Http\Controllers;
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 Neha Khan does not exist in the table :students";
      }
   }
}
?>

Record with name Neha Khan does not exist in the table :students

Conclusion

Laravel's query builder offers multiple ways to count records: count() for simple counting, selectRaw() for complex queries, and exists()/doesntExist() for efficient existence checking without actual counting.