CODEWITHSUNDEEP
laravellaravel-5eloquent
Miura-shi
Miura-shi

Reputation: 4519

Get Past 7 Days Data From Model?

I am trying to query my model and return an object to feed to chart.js 

// Configure dates
$today = Carbon::today();
Carbon::setTestNow($today->subWeek());
$sunday    = new Carbon('this sunday');
$monday    = new Carbon('this week');
$tuesday   = new Carbon('this tuesday');
$wednesday = new Carbon('this wednesday');
$thursday  = new Carbon('this thursday');
$friday    = new Carbon('this friday');
$saturday  = new Carbon('this saturday');
// Return object for charts.js
return response()->json([
    'sunday'     => Event::where('page_id', 2)->where('created_at', 'like', $sunday->toDateString().'%')->get()->count(),
    'monday'     => Event::where('page_id', 2)->where('created_at', 'like', $monday->toDateString().'%')->get()->count(),
    'tuesday'    => Event::where('page_id', 2)->where('created_at', 'like', $tuesday->toDateString().'%')->get()->count(),
    'wednesday'  => Event::where('page_id', 2)->where('created_at', 'like', $wednesday->toDateString().'%')->get()->count(),
    'thursday'   => Event::where('page_id', 2)->where('created_at', 'like', $thursday->toDateString().'%')->get()->count(),
    'friday'     => Event::where('page_id', 2)->where('created_at', 'like', $friday->toDateString().'%')->get()->count(),
    'saturday'   => Event::where('page_id', 2)->where('created_at', 'like', $saturday->toDateString().'%')->get()->count()
]);

The above returns the following:

{
  "sunday": 0,
  "monday": 6,
  "tuesday": 8,
  "wednesday": 0,
  "thursday": 0,
  "friday": 7,
  "saturday": 0
}

There are a few issues however. There should be a total of 24 records however it is only returning 21. Also, it seems like horrible practice to make individual queries for each day. I'd like to query once and then filter down for each day to set a total tally/count. What would be the preferred and most accurate method to return a count of the past 7 days of events? Days missing need to return 0 as well.

Upvotes: 0

Views: 3875

Answers (1)

jreikes
jreikes

Reputation: 714

I'm not 100% sure I understand your question, but I believe this is what you're trying to do...

$today = Carbon::today();
$events = Event::where('created_at', '>', $today->subDays(7))->get();
$totalCount = $events->count(); //Should return your total number of events from past 7 days
$response = array();
$i = 0;
while ($i < 7) {
    $dayOfWeek = $today->subDays($i);
    $eventsForThisDay = $events->where('created_at', $dayOfWeek);
    $response[$dayOfWeek] = $eventsForThisDay->count();
    $i++;
}
return response()->json($response);

Upvotes: 2

Related Questions