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

Question

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.

it can't work...can you be more specific? Are there errors in your logs? Do you see an error in your browser? — Jeremy Harris
– Jeremy Harris, Commented Nov 20, 2015 at 2:43
the error is "Call to a member function groupBy() on integer" — Muhammad Haryadi Futra
– Muhammad Haryadi Futra, Commented Nov 20, 2015 at 2:44

Jeremy Harris · Accepted Answer · 2015-11-20 03:07:31Z

2

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

edited Nov 20, 2015 at 3:07

answered Nov 20, 2015 at 2:45

Jeremy Harris

24.7k13 gold badges84 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Muhammad Haryadi Futra Over a year ago

but i only get one result using this query, how to get all result?

Jeremy Harris Over a year ago

If you want all of the results, count() is not what you want to do. That method counts the number of records that match and then returns the total count. If you want the records instead of the count, replace ->count('*') with ->get()

Muhammad Haryadi Futra Over a year ago

but i want to get all of the date and the number of its appearance

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related