Laravel select with count(*) using db raw example

Hi Guys,

In this small article. i will let you know how to use select with count aggregate and group by using db raw in Laravel application. Here i gave you simple query example for join with count(*) as aggregate in select statement, i convert it into laravel db query builder.

If you want to use count aggregate function in select statement then you require to use db::raw in eloquent select function in Laravel 5 application. If you write query in SQL then it is very easy to write as like bellow:

SQL Query:

SELECT 
	users.id, users.name, COUNT(click.*) as count_click
FROM
	users
INNER JOIN click ON click.user_id = users.id
GROUP BY users.id

As we write above sql query, we have to convert it into laravel db query. It's not any default provided query in laravel. But we can use it by using DB::raw. So let's see bellow example.

DB Query:

$data = DB::table("users")
	    ->select("users.id", "users.name", DB::raw("COUNT(click.*) as count_click"))
	    ->join("click","click.user_id","=","users.id")
	    ->groupBy("users.id")
	    ->get();

print_r($data);

As you can see above, i convert sql into db raw query as like above.

I hope you found your best solution....