Spread the love

MySQL count method gives the total number of rows in executed query, In Laravel we use eloquent or query builder to execute the query. In Laravel eloquent or query builder we use count method to count the rows. We can also count rows after executing the query using get() or all() and then can apply collection count method.

Here is the syntax for count

User::where("status",1)->count();  // uses mysql count method and more performant

//or

User::where("status",1)->selectRaw("count(column_name_or_*) as counter")->first();  // uses mysql count method and more performant

//or

$users=User::where("status",1)->get(); //execute the query  and use laravel collection count method
$users->count(); // then count all collection

SQL of above query will be

select count(*) from `table` where  `status` = 1

//OR

select count(column_name_or_*) from `table` where  `status` = 1

//OR

select * from `table` where  `status` = 1 

//then count collection of  rows 

So let’s understand count rows with examples

Example 1 – Laravel count with mysql count method (Recommended)

In this example I will show to use MySQL count method in laravel8 eloquent and its recommended since it’s more performant. In this method MySQL count all rows without return all rows data so if there is 10000 rows in database then it will simply return the count only

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\User;
  
class ArticleController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $Article = Article::select("*")
                        ->where("status",1)
                        ->count();
      \\or
      DB::table('articles')->where("status",1)->count();

   }
}

Output will be :

select count(*) from `articles` where `status` = 1

Example 2 – Laravel count with laravel collection count method

In this example I will show to use laravel eloquent method in eloquent and it’s not recommended since it return all rows and then perform laravel count method so it can be slow in can of records are high.

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\User;
  
class ArticleController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $Article = Article::select("*")
                        ->where("status",1)
                        ->count();
        $Article->count();
    }
}

Output will be :

select * from `articles` where `status` = 1 

// and then we use laravel collection count
 $Article->count();
 

Leave a Reply