CODEWITHSUNDEEP
mysqlsqllaravelcounteloquent
user8310317
user8310317

Reputation:

Count & GroupBy Function Laravel

I'm building some analytical stat graphs for an administrator dashboard using Laravel. I'm having a lot of fun. Though I'm having a bit of difficulty understanding how to utilize the SQL queries and GroupBy function to achieve some stuff.

End goal: retreive the count of users who were created and groupBy the week they registered.

So far I'm using this:

DB::table("users")
   ->select(DB::raw("COUNT(*) as count"))
   ->orderBy("created_at")
   ->groupBy(function ($query)
     {
       return \Carbon\Carbon::parse($query->created_at)->format("W");
     })
   ->get();

I'm getting an error in Tinker (which is where I do a majority of my testing at the moment it's something like this:

PHP warning:  strtolower() expects parameter 1 to be string,  object given in 
/Users/Ian/sites/bangerz-army/vendor/laravel/framework/src/Illuminate/Database/Grammar.php on line 58

I had gotten a similar query to work in PHP but for performance reasons I'd prefer to keep as much of the math in SQL as possible.

/ Laravel 5.5

Upvotes: 1

Views: 3456

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

Try using DB::raw() in your group by

DB::table("users")
   ->select(DB::raw("COUNT(*) as count, WEEK(created_at)"))
   ->orderBy(DB::raw('WEEK(created_at)'))
   ->groupBy(DB::raw('WEEK(created_at)'))
   ->get();

Upvotes: 1

Related Questions