CODEWITHSUNDEEP
phpsqllaravel-5
Muhammad Haryadi Futra
Muhammad Haryadi Futra

Reputation: 263

How to use COUNT, GROUP BY, and DATE_FORMAT in one Laravel 5 SQL Query?

I want to count the number of date appear in my table using Laravel 5 and SQL Query:

$tanggal = DB::table('entry')->count('*')->groupBy(DATE_FORMAT(created_at,'%d-%m-%Y'));

But it can't work. Help me.

Upvotes: 1

Views: 6311

Answers (1)

Jeremy Harris
Jeremy Harris

Reputation: 24599

When you call count(), it is returning the number (as you saw in your error). Try swapping the functions and setting the groupBy parameter to be calculated raw:

$tanggal = DB::table('entry')
             ->groupBy(DB::raw("DATE(created_at)")
             ->count('*');

When you call table() it starts a query builder instance. A lot of query builder methods return the query builder again for method chaining, but some of them are final and return a result like get(), first(), and in this case count().

I haven't tested the above code, but it should be close. You will also notice I changed it from DATE_FORMAT to just DATE. I can see you are trying to exclude the timestamp portion, and you can just use the latter instead of reformatting (which may slow down the query a tiny bit).

EDIT:

Ok, your question was not explained very well. From your comment, I think this is what you are looking for. Also, don't forget, Eloquent is a fantastic ORM, but it isn't a silver bullet and sometimes you may have to just raw plain SQL queries.

$results = DB::table('entry')
             ->addSelect(DB::raw('COUNT("*") as total)')
             ->groupBy(DB::raw("DATE(created_at)")
             ->get();

Upvotes: 2

Related Questions