Support the ongoing development of Laravel.io →
Eloquent
Last updated 2 years ago.
0

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 :)

Last updated 2 years ago.
0

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')
])
Last updated 2 years ago.
0

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.

Last updated 2 years ago.
0

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

Last updated 2 years ago.
0
Solution

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?

Last updated 2 years ago.
0

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!

Last updated 2 years ago.
0

You are welcome, just mark this thread as solved when you reach a solution or let me know if you need anything else :)

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.