Mushr00m
6 years ago
4408
16
Eloquent

Eloquent : Count of posts for a period with one row per day

Posted 6 years ago by Mushr00m

Hi,

I need to create a graph for the last 30 days, so I need the count of post created per day. Right now I'm having this :

$chartDatas = Post::select([
    DB::raw('DATE(created_at) AS date'),
    DB::raw('COUNT(id) AS count'),
 ])
 ->whereBetween('created_at', [Carbon::now()->subDays(30), Carbon::now()])
 ->groupBy('date')
 ->orderBy('date', 'ASC')
 ->get()
 ->toArray();

This is working but I just have the count for days that actualy have a post created. I need to have 0 for the days that doesn't have a post. So I should have 30 rows for each query as I need the last 30 days, and have 0 on the count column when no post exists on that day.

Thanks for your help !

Please sign in or create an account to participate in this conversation.