Hello,
it's not an easy thing to do with only SQL sintax (as you can see in the SO answer), but with Laravel you can just create a custom collection type ( http://laravel.com/docs/eloquent#collections ) and fill it when it's retrieved :)
It's definitely not easy via sql, that's for sure. Below is the query I currently have. And it does about 99% of what it needs to. Except for give me the weeks that have "0". And that's where my laravel-fu falls apart
Rating::where('created_at', '>=', $daysInThePast)
->groupBy('week')
->groupBy('year')
->groupBy('rating')
->orderBy('week', 'DESC')
->get([
DB::raw('rating'),
DB::raw('Year(created_at) as year'),
DB::raw('Week(created_at) as week'),
DB::raw('COUNT(*) as count')
])
Yes, I understood, but mysql can't give you those weeks magically. You should fill the retrieved collection afterwards.
PS: Check the SQL executed, I am not sure this group by is in the correct order.
How would you recommend getting the weeks that aren't generated by sql? I can understand how to do it with days, but my brain stops when thinking about it as weeks
What I would do is to create a custom collection type and fill the not present weeks. If it sounds difficult, you can just treat it as an array and fill it. I actually had this same issue and what I did (more or less) is:
- Fill an array with ALL the weeks that should be present, in this format: array('2014-03' => 0, '2014-04' => 0, ....); where 03 and 04 are the week numbers.
- iterate over the retrieved array by your SQL expression, update the array you created before with the key "year-week" and the value from the SQL.
And that's all :) did you get the idea?
yeah i think so! at the very least I finally have a little bit of a direction and can play around with that. thank you!
You are welcome, just mark this thread as solved when you reach a solution or let me know if you need anything else :)
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast